Repeat Power Query for different Tables in the same workbook

[Anonymous]

Hello everybody!

Currently I have the following situation:

- One excel file with 15 tables, for 15 company metrics by day and by employee.

Metric1:

Name	01/01/2020	01/02/2020
Employee1	1%	10%
Employee2	3%	5%

- Each of the tables needs to be unpivoted and some other steps. I managed to write a power query for every one of them 

- After those queries, I have one giant Join between the 15 tables to get a complete table, like this

Date	Name	Metric1	Metric 2
01/01/2020	Employee1	1%	127
01/01/2020	Employee2	3%	256

So, the M code maintenance is a really time-consuming job, because there are something like 40+queries. 

I searched for the topic but only found tutorials on applying a custom function to a folder with different files. Is there something I'm missing? How can just one query be iterated to all the tables within the file?

Thank you!

[mahoneypat]

I agree you could make a function to do this.  If you post a sample file with dummy data with 2+ tables, the community could provide the M code to do it.  I also would suggest you append the data from each file, and then either leave it in that format or pivot it out based on table/worksheet name (and then renamed, if needed).

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

Hello @mahoneypat, 

I made a simplified version, can you check it out? I dont have PBI on this computer so I did in excel with PowerQuery, but is the same concept.

https://drive.google.com/file/d/1GxP_gn3B470zJo7ZgXJ-Mf6bMqtsUSrV/view?usp=sharing

[AlB]

Hi @Anonymous 

This is to be run on the excel file with only the original data tables, without the added queries you had, so that Excel.CurrentWorkbook() in the main query yields only the data tables. Load the resulting table(s) as connection only direct into the data model. Otherwise you will have a feedback loop and Excel.CurrentWorkbook() will also load the resulting query as table in the main query

1. Create this function, name it "processTableFunct_" :

(inputT as table, tableName as text )=>
let
    number_cols_not_2_unpivot = List.Count(Table.ColumnNames(inputT)) - List.NonNullCount( List.Transform(Table.ColumnNames(inputT), each try Date.From(_) otherwise null)),
    cols_names_not_2_unpivot = List.FirstN(Table.ColumnNames(inputT), number_cols_not_2_unpivot),
    unpivotedT = Table.UnpivotOtherColumns(inputT, cols_names_not_2_unpivot, "Data", "Valor"),
    output_ = Table.AddColumn(unpivotedT,"Input table name", each tableName)
in
    output_

 2. Create the main query (uses the function above)

let
    Source = Excel.CurrentWorkbook(),
    #"Added Custom" = Table.AddColumn(Source, "processed_tables", each processTableFunct_([Content],[Name])),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content", "Name"}),
    #"Expanded processed_tables" = Table.ExpandTableColumn(#"Removed Columns", "processed_tables", {"Tecnico", "Data", "Valor", "Input table name"}, {"Tecnico", "Data", "Valor", "Input table name"}),
    #"Pivoted Column" = Table.Pivot(#"Expanded processed_tables", List.Distinct(#"Expanded processed_tables"[#"Input table name"]), "Input table name", "Valor", List.Sum)
in
    #"Pivoted Column"

Again, I would recommend to use the format that you get in the step #"Expanded processed_tables" rather than the one you get at the end of the query (what you were looking for). It would also probably be better, depending on what you need, to have the data tables in a separate excel file from the one you're using to process them.

[Anonymous]

Thank you so much @AlB!

I'm really new to functions and parameters, so I don't know if I did something wrong, because in the step 2 I got the error:

It means that field "Data" already exists in the register.

All I did was the following:

1. Deleted all the 4 queries

2. Created one blank query and pasted the first code, renamed it to processTableFunct_

3. Created one blank query and pasted the second code

Is there maybe some step before or after that I missed? Like I said, I don't know much about functions so if you could be very specific I appreciate it.

Can you check it out, please? I included the updated file if it helps

https://drive.google.com/file/d/1mfiB7r_Sc9TdZyjeqCrjogDOlXcNwCfL/view?usp=sharing

[AlB · Accepted solution]

@Anonymous 

I said leave only the tables with data. You have Planinha1 and Join as worksheet still. Why? Remove them. Then it will work. An alternative is to remove the corresponding rows for those two worksheets in the M code, immediately after the Source step in the main query. Again, do not load the result of the main query (Consulta1) to an excel table, load it through a connection only into the data model. And again, once this works I would recommend keeping the data tables in one file and the queries and processing in another one (you would only have to change the way you get your tables at the beginning of the main query, since it will not be with Excel.Currentbook( ))

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers 

[AlB]

Hi @Anonymous 

Why are there 40 queries if you have 15 tables?

Sure, you should definitely use a function. If the steps to be applied to each table are the same (or very similar). You can load all the files in the folder (it's very simple, just:  New Source-->Folder ) you'll then get a table that shows all files, one per row. You can then create a custom column that will apply the function to each file (extracting the table of interest and transforming it as required). Finally you can join  all the resulting tables. 

You can use the code you already have to transform one table to create the function.  By the way the final format you would want would be something like the table below. You'd need some additional (un)pivoting:

Date	Name	Type of metric	Result
01/01/2020	Employee1	Metric1	1%
01/01/2020	Employee1	Metric2	127
01/01/2020	Employee2	Metric1	3%
01/01/2020	Employee2	Metric2	256

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs

Cheers