Forum Discussion
Helper IILoop through a table using M
This function meets your reqs I think.
CombineKeyValue
(InputTable as table, CodeValue as text) as text => [ TargetRows = Table.SelectRows( InputTable, each [Code] = CodeValue and Text.Trim([Value]) <> "" and [Value] <> null ), Parts = Table.TransformRows(TargetRows, each [Key] & "=" & [Value]), Output = Text.Combine(Parts, ",") ][Output]If we assume the name of your original table/query is "Original", then we can invoke like so:
Output:
Super UserThis function meets your reqs I think.
CombineKeyValue
(InputTable as table, CodeValue as text) as text =>
[
TargetRows = Table.SelectRows(
InputTable, each [Code] = CodeValue and Text.Trim([Value]) <> "" and [Value] <> null
),
Parts = Table.TransformRows(TargetRows, each [Key] & "=" & [Value]),
Output = Text.Combine(Parts, ",")
][Output]
If we assume the name of your original table/query is "Original", then we can invoke like so:
Output:
hczeThis is a very elegant solution! Cheers!
v-dineshyaCommunity Support
Hi hcze ,
Thank you for reaching out to the Microsoft Community Forum.
Please follow below steps.
1. Created sample table (Data) based on your inputs.
2. Created ":BuildString" funtion with below M code.
(BuildTable as table, CodeInput as text) as text =>
let
Filtered = Table.SelectRows(BuildTable, each [Code] = CodeInput and [Value] <> null),
Rows = Table.ToRecords(Filtered),
Result = List.Accumulate(
Rows,
"",
(state, current) =>
if state = "" then
current[Key] & "=" & Text.From(current[Value])
else
state & "," & current[Key] & "=" & Text.From(current[Value])
)
in
Result3. Created "Query1" to pass parameters. with below M code.
let
Result = BuildString(Data, "Query 1")
in
Result4. I have passed ":Query 1" as a parameter of "Data Table".
Please find attached PBIX file for your reference.
If this information is helpful, please “Accept it as a solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.