Forum Discussion
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 | 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 |
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.
2 Replies
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 Combinedlet 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.
