Rearrange columns

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.

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

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

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)