Move from multiple rows to single row

[jcastr02]

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	ReportingMonth	Attribute	Value	CountType
9/26/2024	7	9/1/2024	pwr_activity_cd_close	1	POWER
9/26/2024	7	9/1/2024	pwr_activity_cd_deleted	1	POWER
DESIRED
Act_Dt	Index	ReportingMonth	Attribute	Value	CountType
9/26/2024	7	9/1/2024	pwr_activity_cd_close; pwr_activity_cd_deleted	1	POWER

[lbendlin · Accepted solution]

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]

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