:Stacking Data

[jcastr02]

I have a list of stores that are closing along with the stores that will recieve the volume of that closing store.  There are three recieving stores each with their base volume and added volume.  I"d like to stack the columns so it's all moved into 3 main columns, see below my current and my expected result:  Thank you so much. 

Current:

Closing Store #	Final Store Closure Date	Reporting Week	Change in Status	Receiving Store #1	Receiving Store #1 BASE Volume	Receiving Store #1 Added Volume	Receiving Store #2	Receiving Store #1 BASE Volume	Receiving Store #1 Added Volume	Receiving Store #3	Receiving Store #3 BASE Volume	Receiving Store #3 Added Volume
25	5/12/2025	12/4/2024	New	72	337	9	127	265	3	800	55	5
19	4/29/2025	12/4/2024	Updated	80	383	7	200	105	7	45	125	8
94	3/20/2025	12/4/2024	Changed	100	362	52	201	139	13	111	300	9
93	2/20/2025	12/4/2024	New	111	360	32	200	245	11	122	400	11
76	12/5/2024	12/4/2024	New	122	417	28	35	184	17	134	800	12
19	5/12/2025	12/4/2024	New	134	323	24	36	179	18	28	200	19

Desired: 

Closing Store #	Final Store Closure Date	Reporting Week	Change in Status	Receiving Store	Receiving Store  BASE Volume	Receiving Store  Added Volume
25	5/12/2025	12/4/2024	New	72	337	9
19	4/29/2025	12/4/2024	Updated	80	383	7
94	3/20/2025	12/4/2024	Changed	100	362	52
93	2/20/2025	12/4/2024	New	111	360	32
76	12/5/2024	12/4/2024	New	122	417	28
19	5/12/2025	12/4/2024	New	134	323	24
25	5/12/2025	12/4/2024	New	127	265	3
19	4/29/2025	12/4/2024	Updated	200	105	7
94	3/20/2025	12/4/2024	Changed	201	139	13
93	2/20/2025	12/4/2024	New	200	245	11
76	12/5/2024	12/4/2024	New	35	184	17
19	5/12/2025	12/4/2024	New	36	179	18
25	5/12/2025	12/4/2024	New	800	55	5
19	4/29/2025	12/4/2024	Updated	45	125	8
94	3/20/2025	12/4/2024	Changed	111	300	9
93	2/20/2025	12/4/2024	New	122	400	11
76	12/5/2024	12/4/2024	New	134	800	12
19	5/12/2025	12/4/2024	New	28	200	19

[p45cal · Accepted solution]

See linked-to Excel workbook below.

My query is probably a bit amateurish but seems to work.

I changed the column headers of your source data at row 2 since they didn't seem to go in sequence.

Result query at cell A31.

Version including source data here for pasting in Power Query Advanced Editor:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZGxDsIwDET/JTNSYjtJk5mdjanqUKkVTIgBid/HvoTCUMFQK2edX5zrODpO7uCIfUieA0SIXrWKqOK0PrUOrEVk0FphtxNnc4t+JQStyWRy02F0ZC6uXlF70PN9mR/rgklDFIMACRCF1HVsy1ktANcIlw+yCz5e59sFYAJJsi2eGENkbcH+dh+RNQS+2uDS4fwjij6WgedtZ26r4g62dmxPIZCHbLD0BdsjtzlCEMXwQBZzoEkSt7CJP0H/+3ttThjPwzEDidnyvq1nr1FMLw==", BinaryEncoding.Base64), Compression.Deflate)),{"Closing Store #", "Final Store Closure Date", "Reporting Week" , "Change in Status" , "Receiving Store #1" , "Receiving Store #1 BASE Volume", "Receiving Store #1 Added Volume", "Receiving Store #2", "Receiving Store #2 BASE Volume" , "Receiving Store #2 Added Volume", "Receiving Store #3" , "Receiving Store #3 BASE Volume", "Receiving Store #3 Added Volume"}),

ChangedType = Table.TransformColumnTypes(Source,{{"Final Store Closure Date", type date}, {"Reporting Week", type date}},"en-GB"),
UnpivotedColumns = Table.UnpivotOtherColumns(ChangedType, {"Closing Store #", "Final Store Closure Date", "Reporting Week", "Change in Status"}, "Attribute", "Value"),
SplitColumn = Table.SplitColumn(UnpivotedColumns, "Attribute", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Attribute.1", "Attribute.2"}),
Trimmed = Table.TransformColumns(SplitColumn,{{"Attribute.2", Text.Trim, type text}}),
ReplaceValue = Table.ReplaceValue(Trimmed,null,"Recieving store#",Replacer.ReplaceValue,{"Attribute.2"}),
GroupRows = Table.Group(ReplaceValue, {"Closing Store #", "Final Store Closure Date", "Reporting Week", "Change in Status", "Attribute.1"}, {{"grp", each _, type table [#"Closing Store #"=number, Final Store Closure Date=nullable date, Reporting Week=nullable date, Change in Status=text, Attribute.1=nullable text, Attribute.2=text, Value=number]}}),
InvokedFunction = Table.AddColumn(GroupRows, "fnStuff", each ((tbl)=> Table.Pivot(tbl, List.Distinct(tbl[Attribute.2]), "Attribute.2", "Value"))([grp])),
RemoveColumns = Table.RemoveColumns(InvokedFunction,{"grp", "Attribute.1"}),
ExpandedFn = Table.ExpandTableColumn(RemoveColumns, "fnStuff", {"Recieving store#", "BASE Volume", "Added Volume"})
in
ExpandedFn

Link to workbook: https://app.box.com/s/aj3o10p4mun9nxmlszcyvwn970hqwhzx