Loop through a table using M

[hcze]

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!

[MarkLaf]

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:

[ronrsnfld]

Perhaps, 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
    String

Source Table:

Results:

Key1=name, Key2=address, Key1=name, Key2=address

[SundarRaj]

Hi @hcze, is this what you are looking for?
I'll attach the image of the output and the M code. Thanks

Code:
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
String

[AlienSx]

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}) 
)