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
I think I found solution.
https://community.powerbi.com/t5/Desktop/Append-Columns-into-Column-Sets/m-p/1588923
In my case I have text , text, date, 2text, 2text, 2date, 3text , 3text, 3date .......
And I wish to have them in new table or querry like this
text, text, date
2text, 2text, 2date
3text ......
hope this helps
- Greg_Deckler4 years agoCommunity Champion
DamianL Seems like a straight Append query, Table.Combine. Basically, create a query that loads all the columns. Right click the query and set it to disable load. Then create additional queries, also set to disable load where each selects 3 columns. The first would be text, text, date, the second 2text, 2text, 2date, etc. Then create a final Append query that appends all of those intermediate queries together.