I need to track the performance of one brand (store brand) versus all other brands. I believe this requires a one to many relationship. My data set looks like this:
| Date | Type | Product Number | Sales |
|---|---|---|---|
| 01.2020 | Store Brand | 2345 | 500 |
| 01.2020 | Brand | 1111 | 400 |
| 01.2020 | Brand | 2222 | 425 |
| 01.2020 | Brand | 3333 | 450 |
| 02.2020 | Brand | 1111 | 300 |
| 02.2020 | Brand | 2222 | 325 |
| 02.2020 | Brand | 3333 | 350 |
| 03.2020 | Store Brand | 2346 | 600 |
But I need to transform it so that it will look like this:
| Year | Month | Store Brand Product Number | Brand Product Number | Store Brand Sales | Brand Sales |
|---|---|---|---|---|---|
| 2020 | 1 | 2345 | 1111 | 500 | 400 |
| 2020 | 1 | 2345 | 2222 | 500 | 425 |
| 2020 | 1 | 2345 | 3333 | 500 | 450 |
| 2020 | 2 | 2345 | 1111 | 0 | 300 |
| 2020 | 2 | 2345 | 2222 | 0 | 325 |
| 2020 | 2 | 2345 | 3333 | 0 | 350 |
| 2020 | 3 | 2346 | 1112 | 600 | 0 |
| 2020 | 3 | 2346 | 2223 | 600 | 0 |
| 2020 | 3 | 2346 | 3332 | 600 | 0 |
I have a mapping table that shows which brand products can be compared with a store brand product. Which looks like this:
| Store Brand | Brand |
|---|---|
| 2345 | 1111 |
| 2345 | 2222 |
| 2345 | 3333 |
| 2346 | 1112 |
| 2346 | 2223 |
| 2346 | 3332 |
For every time period in the data set all brand products need to be compared to the store brand. If in a certain period no store brand products were sold, then these need to be listed as 0, or as an empty cell. Vice versa, if only store brands were sold in a certain time period, then the comparable brand sales need to be listed as 0, or as an empty cell.
I am using Excel 365, so if possible, please only post answers that I can implement in Excel 365, because I cannot use Power BI Desktop at the moment.
Please let me know if anything is unclear or if I need to add additional information.
Thank you!
let
_mapping =
let
Source = Web.BrowserContents(
"https://stackoverflow.com/questions/68349163/one-to-many-relationship-of-products-sold-with-different-time-periods?noredirect=1#comment120797753_68349163"
),
#"Extracted Table From Html" = Html.Table(
Source,
{
{"Column1", "DIV.s-table-container:nth-child(6) > TABLE.s-table > * > TR > :nth-child(1)"},
{"Column2", "DIV.s-table-container:nth-child(6) > TABLE.s-table > * > TR > :nth-child(2)"}
},
[RowSelector = "DIV.s-table-container:nth-child(6) > TABLE.s-table > * > TR"]
),
#"Promoted Headers" = Table.PromoteHeaders(
#"Extracted Table From Html",
[PromoteAllScalars = true]
),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{{"Store Brand", Int64.Type}, {"Brand", Int64.Type}}
)
in
#"Changed Type",
_fact =
let
Source = Web.BrowserContents(
"https://stackoverflow.com/questions/68349163/one-to-many-relationship-of-products-sold-with-different-time-periods?noredirect=1#comment120797753_68349163"
),
#"Extracted Table From Html" = Html.Table(
Source,
{
{"Column1", "DIV.s-table-container:nth-child(2) > TABLE.s-table > * > TR > :nth-child(1)"},
{"Column2", "DIV.s-table-container:nth-child(2) > TABLE.s-table > * > TR > :nth-child(2)"},
{"Column3", "DIV.s-table-container:nth-child(2) > TABLE.s-table > * > TR > :nth-child(3)"},
{"Column4", "DIV.s-table-container:nth-child(2) > TABLE.s-table > * > TR > :nth-child(4)"}
},
[RowSelector = "DIV.s-table-container:nth-child(2) > TABLE.s-table > * > TR"]
),
#"Promoted Headers" = Table.PromoteHeaders(
#"Extracted Table From Html",
[PromoteAllScalars = true]
),
#"Changed Type" = Table.TransformColumnTypes(
#"Promoted Headers",
{
{"Date", type text},
{"Type", type text},
{"Product Number", Int64.Type},
{"Sales", Int64.Type}
}
)
in
#"Changed Type",
#"Added Index" = Table.AddIndexColumn(_fact, "Index", 1, 1, Int64.Type),
#"Split Column by Delimiter" = Table.SplitColumn(
#"Added Index",
"Date",
Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),
{"month", "year"}
),
#"Changed Type" = Table.TransformColumnTypes(
#"Split Column by Delimiter",
{{"month", Int64.Type}, {"year", Int64.Type}}
),
#"Reordered Columns" = Table.ReorderColumns(
#"Changed Type",
{"Index", "month", "year", "Type", "Product Number", "Sales"}
),
#"Grouped Rows" = Table.Group(
#"Reordered Columns",
{"year", "month"},
{
{
"ad",
each _,
type table [
Index = number,
month = nullable number,
year = nullable number,
Type = nullable text,
Product Number = nullable number,
Sales = nullable number
]
}
}
),
#"Added Custom" = Table.AddColumn(
#"Grouped Rows",
"Custom",
each
let
x = [ad],
#"Sorted Rows" = Table.Sort(x, {{"Type", Order.Descending}}),
#"Added Custom" = Table.AddColumn(
#"Sorted Rows",
"Brand Sales",
each if [Type] <> "Store Brand" then [Sales] else null
),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Store Brand Product Number",
each if [Type] = "Store Brand" then [Product Number] else null
),
#"Filled Down" = Table.FillDown(#"Added Custom1", {"Store Brand Product Number"}),
#"Added Custom2" = Table.AddColumn(
#"Filled Down",
"Store Brand Sales",
each if [Type] = "Store Brand" then [Sales] else null
),
#"Filled Down1" = Table.FillDown(#"Added Custom2", {"Store Brand Sales"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([Type] = "Brand")),
#"Reordered Columns1" = Table.ReorderColumns(
#"Filtered Rows",
{
"Index",
"year",
"month",
"Type",
"Product Number",
"Sales",
"Brand Sales",
"Store Brand Product Number",
"Store Brand Sales"
}
),
#"Renamed Columns" = Table.RenameColumns(
#"Reordered Columns1",
{{"Product Number", "Brand Product Number"}}
),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns", {"Type", "Sales"}),
#"Reordered Columns2" = Table.ReorderColumns(
#"Removed Columns",
{
"Index",
"year",
"month",
"Store Brand Product Number",
"Brand Product Number",
"Store Brand Sales",
"Brand Sales"
}
)
in
#"Reordered Columns2"
),
#"Added Custom1" = Table.AddColumn(
#"Added Custom",
"Custom.1",
each
if Table.IsEmpty([Custom]) = true then
let
x = [ad],
#"Merged Queries" = Table.NestedJoin(
x,
{"Product Number"},
_mapping,
{"Store Brand"},
"_mapping",
JoinKind.LeftOuter
),
#"Expanded _mapping" = Table.ExpandTableColumn(
#"Merged Queries",
"_mapping",
{"Brand"},
{"Brand"}
),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded _mapping", {"Type"}),
#"Reordered Columns3" = Table.ReorderColumns(
#"Removed Columns1",
{"Index", "year", "month", "Product Number", "Sales", "Brand"}
),
#"Renamed Columns1" = Table.RenameColumns(
#"Reordered Columns3",
{
{"Product Number", "Store Brand Product Number"},
{"Sales", "Store Brand Sales"},
{"Brand", "Brand Product Number"}
}
),
#"Added Custom3" = Table.AddColumn(#"Renamed Columns1", "Brand Sales", each 0),
#"Reordered Columns4" = Table.ReorderColumns(
#"Added Custom3",
{
"Index",
"year",
"month",
"Store Brand Product Number",
"Brand Product Number",
"Store Brand Sales",
"Brand Sales"
}
)
in
#"Reordered Columns4"
else
[Custom]
),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom1", {"Custom.1"}),
#"Expanded Custom.1" = Table.ExpandTableColumn(
#"Removed Other Columns",
"Custom.1",
{
"Index",
"year",
"month",
"Store Brand Product Number",
"Brand Product Number",
"Store Brand Sales",
"Brand Sales"
},
{
"Index",
"year",
"month",
"Store Brand Product Number",
"Brand Product Number",
"Store Brand Sales",
"Brand Sales"
}
),
#"Sorted Rows" = Table.Sort(#"Expanded Custom.1", {{"Index", Order.Ascending}}),
#"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([Index] <> null)),
#"Filled Down" = Table.FillDown(#"Filtered Rows", {"Store Brand Product Number"}),
#"Replaced Value" = Table.ReplaceValue(
#"Filled Down",
null,
0,
Replacer.ReplaceValue,
{"Store Brand Sales"}
)
in
#"Replaced Value"