Forum Discussion
Loop through a table using M
Hi all,
I am trying to create function in Power Query to build a string based on values in a table.
My table is
| Code | Key | Value |
| Query 1 | Key1 | name |
| Query 1 | Key2 | address |
| Query 2 | Key1 | name |
| Query 2 | Key2 | address |
| Query2 | Key3 |
Using Code as an argument, to retrieve all relevant key and value and build the string
The logic is similar to this:
While not rec.EOF
If Value <> null then
str = str & "," & [Key] & "=" & [value]
Endif
rec.Next
Appreciate your help!
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:
6 Replies
my_function = (tbl, code_value) => List.Accumulate( Table.ToList(Table.SelectRows(tbl, (x) => x[Code] = code_value), (x) => x), "", (s, c) => s & (if c{2} is null then "" else "," & c{1} & "=" & c{2}) )Hi hcze, is this what you are looking for?
I'll attach the image of the output and the M code. ThanksCode:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCixNLapUMFTSUfJOrQRReYm5qUqxOmgyRkAqMSWlKLW4GEnSCKc2IzzaoHLGQEpBKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Key = _t, Value = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Key", type text}, {"Value", type text}}),
String = Table.AddColumn ( #"Changed Type" , "Str" , each if [Value] <> " " then "Str" & ", " & [Key] & " = " & [Value] else "Value NA" )
in
StringPerhaps, if a null in the Value column = EOF, then:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCixNLapUMFTSUfJOrQRReYm5qUqxOmgyRkAqMSWlKLW4GEnSCKc2IzzaoHLGQEopNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Key = _t, Value = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Key", type text}, {"Value", type text}}), String = [a=#"Changed Type"[Key], b=#"Changed Type"[Value], c=List.Zip({a,b}), d=List.Select(c, each not (_{1} = "")), e=List.Transform(d, each _{0} & "=" & _{1}), f=Text.Combine(e,", ")][f] in StringSource Table:
Results:
Key1=name, Key2=address, Key1=name, Key2=address
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:
This is a very elegant solution! Cheers!
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.
