cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pbarbosa1969
Regular Visitor

Transform more than 100 Ranges in same worksheet in Tables with Power Query

How can I transform this for PQ Tables?

1. One file, one worksheet

<https://ec.europa.eu/info/sites/default/files/economy-finance/cross_country_tables_ar2021_0.xlsx>

2. More than 100 potential Tables

3. The Same Header in each different Range

4. The Name of table related to Description

 

Thanks in advance

Pedro

1 ACCEPTED SOLUTION
Jakinta
Responsive Resident
Responsive Resident

If you ask to import them in PQ as separate tables named after corresponding Description, IMHO only way to do it is via VBA in Excell. E.g. Recognize, create and name ranges/tables, then import in PQ.

 

Otherwise if you want extra column with table names paste this in blank query.

Enter your FilePath and FileName inbetween the quotes at the beginning.

 

let
    Source = Excel.Workbook(File.Contents("FilePath.xlsx"), null, true),
    Navigation = Source{[Item="FileName(without extension)",Kind="Sheet"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(Navigation,{"Column1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if Text.StartsWith([Column2],"Table ") then [Column2] else null),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
    #"Filled Down" = Table.FillDown(#"Removed Errors",{"Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",List.LastN(Table.ColumnNames(#"Filled Down"),1)&List.RemoveLastN(Table.ColumnNames(#"Filled Down"),1)),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Custom] <> null)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith([Column2], "Table ")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
    #"Filtered Rows2" = Table.SelectRows(#"Promoted Headers", each ([Country] <> "Country")),
    FINAL = Table.RenameColumns(#"Filtered Rows2",{{Table.ColumnNames(#"Filtered Rows2"){0}, "Table"}})
in
    FINAL

 

 

View solution in original post

4 REPLIES 4
pbarbosa1969
Regular Visitor

👍Perfect. The solution is clear and it works. In fact, everything is ready to be transformed into a table by selecting the Table Name.

How are the tables generated individually?

Hi @pbarbosa1969 , What do you mean by "How are the tables generated individually"?

By one criterion, to generate the tables all automatically. In other words, I have 150 different Table Names and manage to generate the 150 tables and not one by one.

Jakinta
Responsive Resident
Responsive Resident

If you ask to import them in PQ as separate tables named after corresponding Description, IMHO only way to do it is via VBA in Excell. E.g. Recognize, create and name ranges/tables, then import in PQ.

 

Otherwise if you want extra column with table names paste this in blank query.

Enter your FilePath and FileName inbetween the quotes at the beginning.

 

let
    Source = Excel.Workbook(File.Contents("FilePath.xlsx"), null, true),
    Navigation = Source{[Item="FileName(without extension)",Kind="Sheet"]}[Data],
    #"Removed Columns" = Table.RemoveColumns(Navigation,{"Column1"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if Text.StartsWith([Column2],"Table ") then [Column2] else null),
    #"Removed Errors" = Table.RemoveRowsWithErrors(#"Added Custom", {"Custom"}),
    #"Filled Down" = Table.FillDown(#"Removed Errors",{"Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",List.LastN(Table.ColumnNames(#"Filled Down"),1)&List.RemoveLastN(Table.ColumnNames(#"Filled Down"),1)),
    #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ([Custom] <> null)),
    #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each not Text.StartsWith([Column2], "Table ")),
    #"Promoted Headers" = Table.PromoteHeaders(#"Filtered Rows1", [PromoteAllScalars=true]),
    #"Filtered Rows2" = Table.SelectRows(#"Promoted Headers", each ([Country] <> "Country")),
    FINAL = Table.RenameColumns(#"Filtered Rows2",{{Table.ColumnNames(#"Filtered Rows2"){0}, "Table"}})
in
    FINAL

 

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors