Forum Discussion

jcastr02's avatar
jcastr02
Post Prodigy
1 year ago
Solved

:Stacking Data

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 DateReporting WeekChange in StatusReceiving Store #1Receiving Store #1 BASE VolumeReceiving Store #1 Added VolumeReceiving Store #2Receiving Store #1 BASE VolumeReceiving Store #1 Added VolumeReceiving Store #3Receiving Store #3 BASE VolumeReceiving Store #3 Added Volume
255/12/202512/4/2024New7233791272653800555
194/29/202512/4/2024Updated8038372001057451258
943/20/202512/4/2024Changed10036252201139131113009
932/20/202512/4/2024New111360322002451112240011
7612/5/202412/4/2024New12241728351841713480012
195/12/202512/4/2024New13432324361791828200

19

 

Desired: 

Closing Store #Final Store Closure DateReporting WeekChange in StatusReceiving Store Receiving Store  BASE VolumeReceiving Store  Added Volume
255/12/202512/4/2024New723379
194/29/202512/4/2024Updated803837
943/20/202512/4/2024Changed10036252
932/20/202512/4/2024New11136032
7612/5/202412/4/2024New12241728
195/12/202512/4/2024New13432324
255/12/202512/4/2024New1272653
194/29/202512/4/2024Updated2001057
943/20/202512/4/2024Changed20113913
932/20/202512/4/2024New20024511
7612/5/202412/4/2024New3518417
195/12/202512/4/2024New3617918
255/12/202512/4/2024New800555
194/29/202512/4/2024Updated451258
943/20/202512/4/2024Changed1113009
932/20/202512/4/2024New12240011
7612/5/202412/4/2024New13480012
195/12/202512/4/2024New2820019
Need Help
  • p45cal's avatar
    p45cal
    1 year ago

    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

     

1 Reply

  • p45cal's avatar
    p45cal
    Solution Supplier
    1 year ago

    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