Your expression is building a query string for use in a Web.Contents call. Different parts of your code each separately compute parameters that should be included in the final query string. These are provided as “query fragment” records, which all need to be combined into a single, consolidated record that’s then passed to Web.Contents:
ProductCodeFragment = GetProductCodeQueryFragment(), // might return [productId = 123]
LimitFragment = GetLimitQueryFragment(), // might return [limit = 100]
FinalQueryParams = (single, consolidated record containing query parameters from all fragment records, such as ProductCodeFragment and LimitFragment),
Result = Web.Contents("some-url", [Query = FinalQueryParams]
Using Power Query’s built-in functionality, combining the fragment records into a consolidated record is easy, so long as the fragment records each have different fields. If they do, a record merge can be performed using the combination operator (&) or the records can be combined using Record.Combine, both of which produce the same output.
[productId = 123] & [limit = 100] // outputs [productId = 123, limit = 100]]
Record.Combine({[productId = 123], [limit = 100]}) // outputs [productId = 123, limit = 100]
The challenge comes if multiple records contain the same field—say, one fragment record contains [productId = 123] while another contains [productId = 456]. Record.Combine and & are exclusive in how they compute the field values they output. When the same field name is present in multiple input records (e.g. both input records contain field productId), the value for that field that’s output will be the value from the last/right-most input record (in this case, productId = 456). The other input record value(s) for that field will be ignored (so in this case, productId = 123 is ignored).
// notice that productId 123 is *not* included in the outputs
[productId = 123] & [productId = 456] // outputs [productId = 456]
Record.Combine({[productId = 123], [productId = 456]}) // outputs [productId = 456]
This “last value wins” behavior is valid behavior—but it is not always the desired behavior. In the case of query string composition (what we’re trying to do), what’s needed is not exclusiveness but rather inclusiveness. We want both productId values to be in the output record, so that both will be included in the query string. Specifically, the values for that field from the two records should be combined into a list which is then output as the new productId value: [productId = {123, 456}].
Here’s a helper function that does just this. It combines records inclusively. If only one input record contains the field, in the new record, its value is output verbatim. If multiple records contain the same field, their values are output in the new record in a flattened list.
// each field only present in one input, each value output verbatim
InclusiveRecordCombine({ [productId = 123], [limit = 100] }) // outputs [productId = 123, limit = 100]
// same field present in multiple inputs - all values output in a list
InclusiveRecordCombine({ [productId = 123], [productId = 456] }) // outputs [productId = {123, 456}]
// notice that the 2nd record's list is flattened into the list that's output
InclusiveRecordCombine({ [productId = 123], [productId = {456, 789}] }) // outputs [productId = {123, 456, 789}]
Source Code + Tests
let
InclusiveRecordCombine = (input as list) as record =>
let
// list of unique field names across all input records
OutputFields = List.Union(List.Transform(input, each Record.FieldNames(_))),
// for a given input record, get the value for fieldName as a list
ForceToList = (input as any) as list =>
if input is list then input
else { input },
// computes the value for the specified fieldName
ComputeFieldValue = (input as list, fieldName as text) as any =>
let
// list where each element represents an input record's value for the field
ValuesList =
List.Transform(
List.Select(
List.Transform(input, each try Record.Field(_, fieldName)),
each not _[HasError]
),
each _[Value]
)
in
if List.Count(ValuesList) = 1 then ValuesList{0}
else List.Combine(List.Transform(ValuesList, ForceToList)),
// builds a record containing OutputFields, with each field's value computed by ComputeFieldValue
CombinedRecord =
List.Accumulate(
OutputFields,
[],
(state, currentFieldName) => Record.AddField(state, currentFieldName, ComputeFieldValue(input, currentFieldName))
)
in
CombinedRecord,
Tests =
let
TestCases = #table(
{"Inputs", "Expected"},
{
{ {[a=1], [b=2]}, [a=1,b=2] },
{ {[a=1], [a=2]}, [a={1,2}] },
{ {[a={1}], [a=2]}, [a={1,2}] },
{ {[a={1}], [a={2}]}, [a={1,2}] },
{ {[a=1], [a=2]}, [a={1,2}] },
{ {[a=1], []}, [a=1] },
{ {[a={1}], []}, [a={1}] },
{ {[a=1]}, [a=1] },
{ {[a={1}]}, [a={1}] },
{ {[a=null], []}, [a=null] },
{ {[a=null], [a=null]}, [a={null,null}] }
}
),
Actual = Table.AddColumn(TestCases, "Actual", each InclusiveRecordCombine([Inputs])),
Results = Table.AddColumn(Actual, "Passes Test?", each [Actual] = [Expected], type logical)
in
Results
in
Tests
Hi Ben, I took a stab at creating the same function using
Record.ToTableandTable.Group, not sure which is more performant but a fun exercise!let InclusiveRecordCombine = ( Records as list ) as record => let // Converts each record into a table with columns "Name" and "Value" RecordsAsTables = List.Transform( Records, each Record.ToTable( _ ) ), // Appends the list of tables into a single table CombinedTable = Table.Combine( RecordsAsTables ), // Groups the table rows by "Name", and flattens "Value" for each "Name" GroupedTable = Table.Group( CombinedTable, { "Name" }, { "Value", each if List.Count( [Value] ) = 1 then [Value]{0} else List.Combine( List.Transform( [Value], ( Item ) => if Item is list then Item else { Item } ) ) } ), // Converts the table back to a record CombinedRecord = Record.FromTable( GroupedTable ) in CombinedRecord, Tests = let TestCases = #table( {"Inputs", "Expected"}, { { {[a=1], [b=2]}, [a=1,b=2] }, { {[a=1], [a=2]}, [a={1,2}] }, { {[a={1}], [a=2]}, [a={1,2}] }, { {[a={1}], [a={2}]}, [a={1,2}] }, { {[a=1], [a=2]}, [a={1,2}] }, { {[a=1], []}, [a=1] }, { {[a={1}], []}, [a={1}] }, { {[a=1]}, [a=1] }, { {[a={1}]}, [a={1}] }, { {[a=null], []}, [a=null] }, { {[a=null], [a=null]}, [a={null,null}] } } ), Actual = Table.AddColumn(TestCases, "Actual", each InclusiveRecordCombine([Inputs])), Results = Table.AddColumn(Actual, "Passes Test?", each [Actual] = [Expected], type logical) in Results in TestsThanks,
Alex
Thanks for sharing, Alex!