Forum Discussion
Append data from multiply columns
- 4 years ago
Hi all,
I have used the solution from below
https://community.powerbi.com/t5/Desktop/Append-Columns-into-Column-Sets/m-p/1588923
with small modifications to index,
I have unpivoted 30 columns in a set of data 1,2,3 - 1.1, 2.1, 3.1 - 1.2, 2.2, 3.2 - ......
index need a small modification
instead
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpJrSgB0YZ6MJ4RhAfnGyvF6kQrGUF5JkDaCC5nCuHB+WZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"L1-Code" = _t, #"L1-Description" = _t, #"L2-Code" = _t, #"L2-Description" = _t, #"L3-Code" = _t, #"L3-Description" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"L1-Code", type text}, {"L1-Description", type text}, {"L2-Code", type text}, {"L2-Description", type text}, {"L3-Code", type text}, {"L3-Description", type text}}), #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"L1-Code", "L1-Description", "L2-Code", "L2-Description", "L3-Code", "L3-Description"}, "Attribute", "Value"), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Only Selected Columns", {{"Attribute", each Text.AfterDelimiter(_, "-"), type text}}), #"Added Index" = Table.AddIndexColumn(#"Extracted Text After Delimiter", "Index", 1, 1, Int64.Type), #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / 2,0), type number}}), #"Pivoted Column" = Table.Pivot(#"Divided Column", List.Distinct(#"Divided Column"[Attribute]), "Attribute", "Value"), #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}) in #"Removed Columns"I have created index and divided by 3
#"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
#"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / 3,0), type number}}),to create index looks like below:
and then I pivot the column again creating 1,2,3 per row.
thank you
Hi all,
I have used the solution from below
https://community.powerbi.com/t5/Desktop/Append-Columns-into-Column-Sets/m-p/1588923
with small modifications to index,
I have unpivoted 30 columns in a set of data 1,2,3 - 1.1, 2.1, 3.1 - 1.2, 2.2, 3.2 - ......
index need a small modification
instead
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpJrSgB0YZ6MJ4RhAfnGyvF6kQrGUF5JkDaCC5nCuHB+WZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"L1-Code" = _t, #"L1-Description" = _t, #"L2-Code" = _t, #"L2-Description" = _t, #"L3-Code" = _t, #"L3-Description" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"L1-Code", type text}, {"L1-Description", type text}, {"L2-Code", type text}, {"L2-Description", type text}, {"L3-Code", type text}, {"L3-Description", type text}}),
#"Unpivoted Only Selected Columns" = Table.Unpivot(#"Changed Type", {"L1-Code", "L1-Description", "L2-Code", "L2-Description", "L3-Code", "L3-Description"}, "Attribute", "Value"),
#"Extracted Text After Delimiter" = Table.TransformColumns(#"Unpivoted Only Selected Columns", {{"Attribute", each Text.AfterDelimiter(_, "-"), type text}}),
#"Added Index" = Table.AddIndexColumn(#"Extracted Text After Delimiter", "Index", 1, 1, Int64.Type),
#"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / 2,0), type number}}),
#"Pivoted Column" = Table.Pivot(#"Divided Column", List.Distinct(#"Divided Column"[Attribute]), "Attribute", "Value"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns"
I have created index and divided by 3
| #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type), #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.RoundUp(_ / 3,0), type number}}), |
to create index looks like below:
and then I pivot the column again creating 1,2,3 per row.
thank you