Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
1 year ago
Solved

Move from multiple rows to single row

How can I get the attribute column into one row when there are multipe rows relating to the same index....see below current and desired result.   Current           Act_Dt Index Reporti...
Need Help
  • lbendlin's avatar
    lbendlin
    1 year ago

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3MtM3MjAyUdJRMgdiS31DGLegvCg+MbkksyyzpDI+OSU+OSe/OBUobgjEAf7hrkFKsTokGZCSmpNakpqCakQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Act_Dt = _t, Index = _t, ReportingMonth = _t, Attribute = _t, Value = _t, CountType = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Act_Dt", "Index", "ReportingMonth", "Value", "CountType"}, {{"Attribute", each Text.Combine([Attribute],"; "), type nullable text}})
in
    #"Grouped Rows"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

dufoq3's avatar
dufoq3
Community Champion
1 year ago

Hi jcastr02, check this:

 

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstQ3MtM3MjAyUdJRMgdiS31DGLegvCg+MbkksyyzpDI+OSU+OSe/OBUobgjEAf7hrkFKsTokGZCSmpNakpqCakQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Act_Dt = _t, Index = _t, ReportingMonth = _t, Attribute = _t, Value = _t, CountType = _t]),
    GroupedRows = Table.Group(Source, {"Index"}, {{"T", each Table.FromRecords({Table.First(Table.RemoveColumns(_, {"Attribute"})) & [Attribute = Text.Combine([Attribute], "; ") ]}) , type table}}, 0),
    Combined = Table.Combine(GroupedRows[T], Value.Type(Table.FirstN(Source,0)))
in
    Combined