Forum Discussion
Solved
Several tabs to one table
Dear all,
I would like some help to transform the data in the attached excel file into one table. As you’ll see, we have one tab per quarter. Format is the same for all tabs, with the caveat that the last one might have not all three months but fewer. Info on the different months is in rows rather than in columns:
| Division : Division1 | ||||||||||||||||
| Q3 2019 | ||||||||||||||||
| Division | Shipto | Ba/Di | Brand | SKU | Gross Sales Jul.19 |
NTS Jul.19 |
NIS Jul.19 |
Statistic Orders Jul.19 |
Gross Sales Aug.19 |
NTS Aug.19 |
NIS Aug.19 |
Statistic Orders Aug.19 |
Gross Sales Sep.19 |
NTS Sep.19 |
NIS Sep.19 |
Statistic Orders Sep.19 |
| Division1 | 112233 | Di | Brand1 | 001122 | 111 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| Division1 | 112234 | Ba | Brand2 | 001133 | 555 | 556 | 557 | 558 | 559 | 560 | 561 | 562 | 563 | 564 | 565 | 566 |
I would like to have all data in one output table with a column for the year and a column for the month. Something like this. Note I'm only showing the first quarter for the example but all data for the year should be in that final table
| Division | Shipto | Ba/Di | Brand | SKU | Gross Sales | NTS | NIS | Statistic Orders | Year | Month |
| Division1 | 112233 | Di | Brand1 | 001122 | 1 | 2 | 3 | 4 | 2.019 | Jan |
| Division1 | 112234 | Ba | Brand2 | 001133 | 5 | 6 | 7 | 8 | 2.019 | Jan |
| Division1 | 112233 | Di | Brand1 | 001122 | 5 | 6 | 7 | 8 | 2.019 | Feb |
| Division1 | 112234 | Ba | Brand2 | 001133 | 9 | 10 | 11 | 12 | 2.019 | Feb |
| Division1 | 112233 | Di | Brand1 | 001122 | 9 | 10 | 11 | 12 | 2.019 | Mar |
| Division1 | 112234 | Ba | Brand2 | 001133 | 13 | 14 | 15 | 16 | 2.019 | Mar |
Can you give me a hand with this?
Many thanks
ImkeF, Anonymous Nathaniel_C , Mariusz
Hi AlB ,
this solution uses a parameter "FilePath" in step "Source" - just use/replace with path to your xlsx-file:
let fnOneTable = (MyTable as table) => let #"Filtered Rows" = Table.SelectRows(MyTable, each ([Column2] <> null)), #"Promoted Headers1" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers1", {"Division", "Shipto", "Ba/Di", "Brand", "SKU"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.2", type date}}), #"Inserted Year" = Table.AddColumn(#"Changed Type2", "Year", each Date.Year([Attribute.2]), Int64.Type), #"Extracted Month" = Table.TransformColumns(#"Inserted Year",{{"Attribute.2", Date.Month, Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Extracted Month",{{"Attribute.2", "Month"}}), #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute.1]), "Attribute.1", "Value") in #"Pivoted Column", Source = Excel.Workbook(File.Contents(FilePath), null, true), #"Filtered Rows1" = Table.SelectRows(Source, each ([Name] <> "output")), #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each fnOneTable([Data])), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}), #"Removed Columns" = Table.RemoveColumns(#"Removed Errors",{"Data", "Item", "Kind", "Hidden"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Division", "Shipto", "Ba/Di", "Brand", "SKU", "Month", "Year", "Gross Sales", "NTS", "NIS", "Statistic Orders"}, {"Division", "Shipto", "Ba/Di", "Brand", "SKU", "Month", "Year", "Gross Sales", "NTS", "NIS", "Statistic Orders"}) in #"Expanded Custom"
2 Replies
Hi AlB ,
this solution uses a parameter "FilePath" in step "Source" - just use/replace with path to your xlsx-file:
let fnOneTable = (MyTable as table) => let #"Filtered Rows" = Table.SelectRows(MyTable, each ([Column2] <> null)), #"Promoted Headers1" = Table.PromoteHeaders(#"Filtered Rows", [PromoteAllScalars=true]), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers1", {"Division", "Shipto", "Ba/Di", "Brand", "SKU"}, "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Attribute.2", type date}}), #"Inserted Year" = Table.AddColumn(#"Changed Type2", "Year", each Date.Year([Attribute.2]), Int64.Type), #"Extracted Month" = Table.TransformColumns(#"Inserted Year",{{"Attribute.2", Date.Month, Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Extracted Month",{{"Attribute.2", "Month"}}), #"Pivoted Column" = Table.Pivot(#"Renamed Columns", List.Distinct(#"Renamed Columns"[Attribute.1]), "Attribute.1", "Value") in #"Pivoted Column", Source = Excel.Workbook(File.Contents(FilePath), null, true), #"Filtered Rows1" = Table.SelectRows(Source, each ([Name] <> "output")), #"Added Custom" = Table.AddColumn(#"Filtered Rows1", "Custom", each fnOneTable([Data])), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}), #"Removed Columns" = Table.RemoveColumns(#"Removed Errors",{"Data", "Item", "Kind", "Hidden"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Division", "Shipto", "Ba/Di", "Brand", "SKU", "Month", "Year", "Gross Sales", "NTS", "NIS", "Statistic Orders"}, {"Division", "Shipto", "Ba/Di", "Brand", "SKU", "Month", "Year", "Gross Sales", "NTS", "NIS", "Statistic Orders"}) in #"Expanded Custom"
