Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
1 year ago
Solved

Rearrange columns

I am trying to rearrange my columns in power query to stack on top of each other.  I have to stack all like columns together...see example below and desired outcome.  How could I achieve this in PQ? ...
Need Help
  • PwerQueryKees's avatar
    PwerQueryKees
    1 year ago

    Three stores I see. I would use the same approach.
    Any number of stores?
    Is doable, but will take som complex coding.
    Something along the lines of:

    1. Demote the headers (if you didn't already)
    2. Make a list of all the sub tables by looking at the total number of columns and using Table.Columnnames() and List.Range to get the column names to select
    3. Table.Combine(the_list_you_just_created) 
p45cal's avatar
p45cal
Solution Supplier
1 year ago

Maybe something along the lines of:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TZJbb6swDMe/itXn6SgXro9pGzZKSqqEFaGpD0hDZ0jrKq3t+fzHNudSBIlx8o9/tvP2tpIqL+XqaaWyFEfxQ9Ck8UuFgLVxXb33wUJrutq3xsGhbiyuust9vv6aPz8n/NlvcUhEmZH08RwpeCigh4051J13sA3oipf77QPnvqZ1pRMCEJKFioSKPLLMkOHgrImm7SB26DNd5+zaB4925VanJ8wglUXJcR4i50ykoHK+r9tniIeAU4TeDAQ8j+eZ4h9Jp1XGAFKxXjIAkyuI0Jl9jS90wVHe4/19+n4fOe+KPAU+rE5YnZCaayB1nsH1PN8+IFCBMAnY+UjVa4eFPC8TxTvLJTLbHBjRd7aqbIi+BXO0f6Ghn663f+RCLLG1+K8nU8ul3tivtTtScJJBQOz5JxW+3ZAMH9qcL+SayUs+T2nYjefpuhQ9+E1TeU6ibhfyQki+Ncljt8mjcQXbvTd1u4gXbDsydjMsjVLUH5WqBzGjqFSAM42NL3TpIkmq7+n6dUFjY2hboUvB7H/KTTeVpEkiCzga5+wA6+B9AxzQDe325TUwCdbsdPoN", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Receiving Store #1" = _t, Base = _t, Retention = _t, Sales = _t, Address = _t, City = _t, State = _t, #"Receiving Store #2" = _t, Base.1 = _t, Retention.1 = _t, Sales.1 = _t, Address.1 = _t, City.1 = _t, State.1 = _t, #"Receiving Store #3" = _t, Base.2 = _t, Retention.2 = _t, Sales.2 = _t, Address.2 = _t, City.2 = _t, State.2 = _t]),
    Hdrs = List.FirstN(Table.ColumnNames(Source),7),
    AddedCustom = Table.AddColumn(Source, "Custom", each Record.ToList(_)),
    AddedCustom1 = Table.AddColumn(AddedCustom, "Custom.1", each List.Split([Custom],7)),
    RemovedColumns = Table.SelectColumns(AddedCustom1,{"Custom.1"}),
    ExpandedC = Table.ExpandListColumn(RemovedColumns, "Custom.1"),
    AddedCustom2 = Table.AddColumn(ExpandedC, "Custom", each Record.FromList([Custom.1],Hdrs)),
    RemovedColms = Table.RemoveColumns(AddedCustom2,{"Custom.1"}),
    ExpandedCustom = Table.ExpandRecordColumn(RemovedColms, "Custom", Hdrs)
in
    ExpandedCustom

 

 

 

Where the starting table should be as wide as necessary. There's probably a slicker way. First column always headed "Receiving Store #1" even if it's not #1. There are several hard-coded 7s in the code; the number of columns to split by. In the example above all the data types are text but if you do this from typed data that data type will be retained cell by cell (not column by column).

 

Completely separately, if your table is in Excel, you can use a formula on the data body of your table:

=WRAPROWS(TOCOL(A2:U5),7)