Transform multiples tables in a sheet into a data table

[Anonymous]

Hi everyone,

I have a file that has multiple tables nested within a sheet. I just need it to transform it into a data table and add 2 columns on the last. Spending a lot of time figuring out how to transform this one.

Here's the raw data:

And this what I want to do:

Thanks to all in advance!

[mussaenda · Accepted solution]

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxxxxxxx\Downloads\Help PowerBI.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(Sheet1_Sheet,{"Column4", "Column5", "Column7", "Column9"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom2", each if [Column1] = null and [Column2] = null and [Column3] = null and [Column6] = null and [Column8] = null and [Column10] = null and [Column11] = null and [Column12]= null then "Hide" else "Show"),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom2] = "Show")),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Filtered Rows", "Column2", "Column2 - Copy"),
    #"Calculated Text Length" = Table.TransformColumns(Table.TransformColumnTypes(#"Duplicated Column1", {{"Column2 - Copy", type text}}, "en-GB"),{{"Column2 - Copy", Text.Length, Int64.Type}}),
    #"Added Conditional Column" = Table.AddColumn(#"Calculated Text Length", "Date", each if [#"Column2 - Copy"] = 10 then [Column2] else null),
    #"Filled Down1" = Table.FillDown(#"Added Conditional Column",{"Date"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Filled Down1",{"Column2 - Copy"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns1",{{"Date", type date}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type2", "Column3", "Column3 - Copy"),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Duplicated Column", {{"Column3 - Copy", each Text.AfterDelimiter(Text.From(_, "en-GB"), " "), type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Extracted Text After Delimiter",{{"Column3 - Copy", Text.Trim, type text}}),
    #"Replaced Value" = Table.ReplaceValue(#"Trimmed Text","",null,Replacer.ReplaceValue,{"Column3 - Copy"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Column3 - Copy"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Filled Down", {{"Column2", type text}, {"Column3", type text}, {"Column12", type text}}, "en-GB"),{"Column1", "Column2", "Column3", "Column6", "Column8", "Column10", "Column11", "Column12"},Combiner.CombineTextByDelimiter("#(tab)", QuoteStyle.None),"Merged"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Merged Columns", "Merged", Splitter.SplitTextByDelimiter("#(tab)", QuoteStyle.Csv), {"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Merged.1", type text}, {"Merged.2", type text}, {"Merged.3", type text}, {"Merged.4", type text}, {"Merged.5", type text}, {"Merged.6", type text}, {"Merged.7", type text}, {"Merged.8", type text}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.7", "Merged.8", "Column3 - Copy", "Custom2"}),
    #"Grouped Rows" = Table.Group(#"Reordered Columns", {"Merged.7"}, {{"Count", each _, type table [Merged.1=text, Merged.2=text, Merged.3=text, Merged.4=text, Merged.5=text, Merged.6=text, Merged.7=text, Merged.8=text, #"Column3 - Copy"=text, Date=date, Custom2=text]}}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Column3 - Copy", "Date", "Merged.1", "Merged.2", "Merged.3", "Merged.4", "Merged.5", "Merged.6", "Merged.8"}, {"Count.Column3 - Copy", "Count.Date", "Count.Merged.1", "Count.Merged.2", "Count.Merged.3", "Count.Merged.4", "Count.Merged.5", "Count.Merged.6", "Count.Merged.8"}),
    #"Removed Top Rows" = Table.Skip(#"Expanded Count",11),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type3" = Table.TransformColumnTypes(#"Promoted Headers",{{"Display Name", type text}, {"Deactivated Users", type text}, {"01/07/2019", type date}, {"Group", type text}, {"", Int64.Type}, {"Sub-Group", type number}, {"User", type text}, {"First Name", type text}, {"LastName", type text}, {"Date Created", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type3",{{"Deactivated Users", "Status"}, {"01/07/2019", "Date"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns", each ([Display Name] <> "")),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows1",{""}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns2",{"Group", "Sub-Group", "User", "First Name", "LastName", "Display Name", "Date Created", "Status", "Date"}),
    #"Duplicated Column2" = Table.DuplicateColumn(#"Reordered Columns1", "Date", "Date - Copy"),
    #"Extracted Month Name" = Table.TransformColumns(#"Duplicated Column2", {{"Date - Copy", each Date.MonthName(_), type text}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Extracted Month Name",{{"Date - Copy", "Month"}}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Renamed Columns1",{{"Group", Int64.Type}})
in
    #"Changed Type4"

Change those in red.

I know there are others who can make the steps shorter. Will be glad to learn from them. But I hope this helps.

[v-piga-msft]

Hi @Anonymous ,

From your information, we may could transform your data in Query Editor, but it will be a little complex.

To be more helpful, please share your sample data in text-tabular format in addition to (or instead of)  screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Or you could upload your sample excel file in OneDrive and past link here. Do mask sensitive data before uploading.

Best  Regards,

Cherry

[Anonymous]

Here's the sample file for your reference. Hope this helps! Thank you so much!