Forum Discussion

hcze's avatar
hcze
Icon for Helper II rankHelper II
1 year ago
Solved

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 Que...
Need Help
  • MarkLaf's avatar
    MarkLaf
    1 year ago

    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:

     

     

     

     

SundarRaj's avatar
SundarRaj
Icon for Super User rankSuper User
1 year ago

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