Mar 27 2020 10:53 AM
I have a sheet called "Sales", where I have my data in a Table, the first column of the table has the "ID Store", the second the "Top product" and the third the "Sales values". I need to put this data in another sheet called "Data", in a horizontal format, where I have a fixed list of "ID Store", I can have ID Stores that are not in the table of the "Sales" sheet.
Example in Excel file as an attachment.
What I have:
What I need to do.
Can you help me, thank you.
Mar 27 2020 05:05 PM
SolutionIn attached file is variant wit Power Query. As the source named range with 3 columns in Sales sheet.
let
Source = Excel.CurrentWorkbook(),
FiltereRange = Table.SelectRows(Source, each ([Name] = "SourceData")),
SourceData = FiltereRange{[Name="SourceData"]}[Content],
FindFirstRow = List.PositionOf(SourceData[Column1],"Store ID"),
RemoveTopRows = Table.Skip(SourceData,FindFirstRow),
PromoteHeaders = Table.PromoteHeaders(RemoveTopRows, [PromoteAllScalars=true]),
ChangeTypeForData = Table.TransformColumnTypes(
PromoteHeaders,
{
{"Store ID", Int64.Type},
{"Top Product", type text},
{"Top Value", type number}
}
),
GroupeByStore = Table.Group(
ChangeTypeForData,
{"Store ID"},
{
{"Product", each Table.Sort(_,{{"Top Value",Order.Descending}})[Top Product]},
{"Value", each Table.Sort(_,{{"Top Value",Order.Descending}})[Top Value]}
}
),
FilterNoStore = Table.SelectRows(GroupeByStore, each ([Store ID] <> null)),
Separators = Table.AddColumn(FilterNoStore, "Custom", each List.Count([Product])),
CalculateMaximum = List.Max(Separators[Custom]),
TopProducts = List.Transform({1..List.Max(Separators[Custom])},each "Top Product " & Text.From(_)),
TopValues = List.Transform({1..List.Max(Separators[Custom])},each "Top Value " & Text.From(_)),
ExtractProducts = Table.TransformColumns(
FilterNoStore,
{"Product", each Text.Combine(List.Transform(_, Text.From), ","), type text}
),
ExtractValues = Table.TransformColumns(
ExtractProducts,
{"Value", each Text.Combine(List.Transform(_, Text.From), ","), type text}
),
SplitProducts = Table.SplitColumn(
ExtractValues,
"Product",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), TopProducts
),
SplitValues = Table.SplitColumn(
SplitProducts,
"Value",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), TopValues
),
ValuesToNumbers = Table.TransformColumnTypes(
SplitValues,
List.Transform(TopValues, each {_, type number})
)
in
ValuesToNumbers
Mar 27 2020 10:24 PM
Mar 27 2020 05:05 PM
SolutionIn attached file is variant wit Power Query. As the source named range with 3 columns in Sales sheet.
let
Source = Excel.CurrentWorkbook(),
FiltereRange = Table.SelectRows(Source, each ([Name] = "SourceData")),
SourceData = FiltereRange{[Name="SourceData"]}[Content],
FindFirstRow = List.PositionOf(SourceData[Column1],"Store ID"),
RemoveTopRows = Table.Skip(SourceData,FindFirstRow),
PromoteHeaders = Table.PromoteHeaders(RemoveTopRows, [PromoteAllScalars=true]),
ChangeTypeForData = Table.TransformColumnTypes(
PromoteHeaders,
{
{"Store ID", Int64.Type},
{"Top Product", type text},
{"Top Value", type number}
}
),
GroupeByStore = Table.Group(
ChangeTypeForData,
{"Store ID"},
{
{"Product", each Table.Sort(_,{{"Top Value",Order.Descending}})[Top Product]},
{"Value", each Table.Sort(_,{{"Top Value",Order.Descending}})[Top Value]}
}
),
FilterNoStore = Table.SelectRows(GroupeByStore, each ([Store ID] <> null)),
Separators = Table.AddColumn(FilterNoStore, "Custom", each List.Count([Product])),
CalculateMaximum = List.Max(Separators[Custom]),
TopProducts = List.Transform({1..List.Max(Separators[Custom])},each "Top Product " & Text.From(_)),
TopValues = List.Transform({1..List.Max(Separators[Custom])},each "Top Value " & Text.From(_)),
ExtractProducts = Table.TransformColumns(
FilterNoStore,
{"Product", each Text.Combine(List.Transform(_, Text.From), ","), type text}
),
ExtractValues = Table.TransformColumns(
ExtractProducts,
{"Value", each Text.Combine(List.Transform(_, Text.From), ","), type text}
),
SplitProducts = Table.SplitColumn(
ExtractValues,
"Product",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), TopProducts
),
SplitValues = Table.SplitColumn(
SplitProducts,
"Value",
Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), TopValues
),
ValuesToNumbers = Table.TransformColumnTypes(
SplitValues,
List.Transform(TopValues, each {_, type number})
)
in
ValuesToNumbers