Rearrange columns

[jcastr02]

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?

Current:
Receiving Store #1	Base	Retention	Sales	Address	City	State	Receiving Store #2	Base	Retention	Sales	Address	City	State	Receiving Store #3	Base	Retention	Sales	Address	City	State
12791	265	0.05	3	500 BALTIMORE NATIONAL PIKE	Louisville	MD	4096	365	0.05	10	108 W CAPITOL DR	South	WI	1234	201	0.25	24	19600 PLEASANT ST	ATTLEBORO	FL
15189	105	0.05	7	402 FLOWING SPRINGS WAY	Miami	WV	13264	212	0.15	20	12 S TAMIAMI TRL	Lauderdale	FL	8888	214	0.45	65	1376 smith RD	SAN JOSE	NY
17942	139	0.1	13	1202 JEFFERSON AVE	Miami West	WV	10088	230	0.1	10	31 CAPITAL BLVD	West Raleigh	NC	2222	374	0.35	69	2323 James ST	ROCKFORD	IN
18011	245	0.05	11	3801 W MAIN ST	Miami East	KY	10527	252	0.05	12	3250 LAKESHORE St	Fresno	CA	18390	224	0.4	52	4418 VALLEY BROOK st	LYNDHURST	WV
Desired:
Receiving Store #1	Base	Retention	Sales	Address	City	State
12791	265	0.05	3	500 BALTIMORE NATIONAL PIKE	Louisville	MD
15189	105	0.05	7	402 FLOWING SPRINGS WAY	Miami	WV
17942	139	0.1	13	1202 JEFFERSON AVE	Miami West	WV
18011	245	0.05	11	3801 W MAIN ST	Miami East	KY
4096	365	0.05	10	108 W CAPITOL DR	South	WI
13264	212	0.15	20	12 S TAMIAMI TRL	Lauderdale	FL
10088	230	0.1	10	31 CAPITAL BLVD	West Raleigh	NC
10527	252	0.05	12	3250 LAKESHORE St	Fresno	CA
1234	201	0.25	24	19600 PLEASANT ST	ATTLEBORO	FL
8888	214	0.45	65	1376 smith RD	SAN JOSE	NY
2222	374	0.35	69	2323 James ST	ROCKFORD	IN
18390	224	0.4	52	4418 VALLEY BROOK st	LYNDHURST	WV

[p45cal]

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)

[PwerQueryKees]

@lbendlin beat me....

But I made this.

Started with

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}}),
    Store_count = List.Count(Table.ColumnNames(#"Changed Type")) / 7,
    Sub_Table_List = List.Accumulate(
            List.Numbers(0,Store_count,7), 
            {}, 
            (State, Current) => 
                List.Combine(
                    {
                        State,
                        {let 
                            column_names = List.Range(Table.ColumnNames(#"Changed Type"),Current, 7),
                            sub_table = Table.SelectColumns(#"Changed Type", column_names),
                            reset_columns = Table.DemoteHeaders(Table.PromoteHeaders(sub_table, [PromoteAllScalars=true]))
                         in
                            reset_columns
                        }
                    }
                )
        ),
    stacked_table = Table.Combine(Sub_Table_List),
    #"Promoted Headers" = Table.PromoteHeaders(stacked_table, [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Receiving Store #1", type any}, {"Base", type any}, {"Retention", type any}, {"Sales", type any}, {"Address", type text}, {"City", type text}, {"State", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [Base] <> "Base")
in
    #"Filtered Rows"

Producing This:

Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZJdr9owDIb/isVuj6YkLdBeBmh3CqFBSQdCiItqROdU4kOi5Uj797OdfaBdTquq2Ery2o8dHw4jF76F7qO7voEfbvcAn+ToZTRr+4DGhSFch+52Rd+359Cj1afTPfTkzbvhOx0M7RAv/xVI/a9AyT8FOr4cRlJNc6pHTca4is+CDIUbCwEzbZpqbV0BtW4qW2sDm2pV4Km5Pbr+ozufKeV6gUsq8glJn+NIwUsGO5jrTdVYAwtH6W+P4R3trqJzlaQEICQLFQkV7ch8ggwbU2iv6wZ8Q5U0jSlm1ln0SxMrGMss5zxPmadMpKA0dlfVX8BvHBoPO70n4K69dJR/S7pETRhAKtZLBmByBR4ava7wh8YZqrt9nML91HLdJe1k+LE6ZXVKau6BTKYT6C/d8A6OGoRFwNJ66l69j+TTPFV8M4+Z2efEiL4syrJw3tagt8UvaNiFfvhNLkTMnYg/enITGfuN7zUzW0pOMnCI3b1R4+s5yfCjy9NInjB5zvFUAsv2EvrYdGfnq9JyEVUdyTMheWrS59emnQRP8LnXuqqjOGIXLWOv9vGhFL2PGqsnMaOosQCjV4V/paHzJClxcq83Gl1N17IkF8z+s900qSRNU5nBVhtT7GHmrF0BJzT7evH61TEJ9ux4/AE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t, Column14 = _t, Column15 = _t, Column16 = _t, Column17 = _t, Column18 = _t, Column19 = _t, Column20 = _t, Column21 = _t]),
    Segments = #table({"Segment"},{{1},{2},{3}}),
    Convert = (tbl,seg)=>
        let
            #"Split Column by Delimiter" = Table.SplitColumn(tbl, "Column" & Text.From(seg*7-6), Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
            #"Removed Columns" = Table.RemoveColumns(#"Split Column by Delimiter",{"Column1.2"}),
            #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns", [PromoteAllScalars=true])
        in
            #"Promoted Headers",
    #"Added Custom" = Table.AddColumn(Segments, "Custom", each Convert(Table.SelectColumns(Source,List.Range(Table.ColumnNames(Source),[Segment]*7-7,7)),[Segment])),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Receiving Store ", "Base", "Retention", "Sales", "Address", "City", "State"}, {"Receiving Store ", "Base", "Retention", "Sales", "Address", "City", "State"})
in
    #"Expanded Custom"

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.

[PwerQueryKees]

Only 2 stores?

• Make a reference to you table
• In your original table Select the first store columns
• In your reference table select the second store columns
• In your reference table 
  Table.Combine({Table, #"Last Step in your reference table query"})

I did this from the top of my head ...

Did I answer your question? Then please mark my post as the solution and make it easier to find for others having a similar problem.
If I helped you, please click on the Thumbs Up to give Kudos.

Kees Stolker

A big fan of Power Query and Excel

[PwerQueryKees · Accepted solution]

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)