Several tabs to one table

[AlB]

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 

[ImkeF · Accepted solution]

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"

[AlB]

Great. Thanks very much @ImkeF