let
Source = Csv.Document(File.Contents("C:\Users\Alison\Downloads\Stock list per store.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"SKU", type text}, {"Total Quantity", Int64.Type}, {"Narooma", type time}, {"Merimbula", type time}, {"Bega", type time}, {"Batemans Bay", type time}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"SKU", "Total Quantity"}, "Attribute", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type1", "Value", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"Value.1", "Value.2"}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Value.2", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Value.2.1", "Value.2.2"}),
#"Removed Columns1" = Table.RemoveColumns(#"Split Column by Delimiter1",{"Value.2.2"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns1", "Custom", each "Store "&[Value.1]&": "&[Attribute]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Value.1"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"SKU", "Total Quantity", "Custom", "Value.2.1"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Reordered Columns",{{"Value.2.1", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(#"Changed Type2", List.Distinct(#"Changed Type2"[Custom]), "Custom", "Value.2.1")
in
#"Pivoted Column"
Remember to adjust the file path.
Bookmarks