cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Borsti
New Member

Multiple tables in worksheet equally transformed into multiple tables in another worksheet

Hello all,

 

I started working with PQ a while ago and my skills are very basic, but so far I was able to do what ever I needed...

 

Now I have a workbook that contains several tables in several worksheets, all tables in each worksheet are built equally and need to be transformed equally. The thing is that the tables are in different categories and subcategories which need to be shown in the resulting tables. The query code for each worksheet is always the same.

 

Right now I take a table, create a query, copy the code of an existing query into the new, load the resulting table in a new worksheet, cut it and place it in the "result" worksheet. This is a lot of manual work, but as this workbook is template, I only have to do it once.

I know that this is for sure the most tortuous way to get the result I want to have, but so far it has been working out. But now I have extended my template and excel crashes when I refresh the queries...

 

So I need to start working on a more smart way to get to where I want to be. Can some of you please help me to start into the right direction?

 

Ideally there would be a code existing that is even doing the creation an stacking of the resulting tables, but I don't think that this is possible w/o some macros (which I am not allowed to use). So I was thinking if there is smarter way of setting up the queries so excel doesn't crash at 40+ queries.

 

Would it help to define a function that does the transformation and just call it in each query? But this would not reduce the amount of queries then...

 

https://docs.google.com/spreadsheets/d/13jSb5dTEnIb3tHjqYelqzUFM8Mig89s2/edit?usp=sharing&ouid=10614... 

 

Cheers

 

 

1 ACCEPTED SOLUTION
Smauro
Solution Sage
Solution Sage

So, first let me say that I haven't read your message in detail.

 

Now, you think well that parameterising and creating a custom function is a good option.

But, you could also do everything in one.

 

Assuming that:

1. All tables are formatted as tables.

2. Any table that you wish to transform this way, can be easily identified by its name (e.g. starts with "CD_")

3. You wish to view the result in one sheet

 

then you can have:

a) A function to format the tables as you wish (named TableToValues -referenced in next query):

(tbl as table) as table =>
let
    #"Removed Columns" = Table.RemoveColumns(tbl,{"Column1", "Column3", "Column4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column2", type text}, {"Column5", type text}, {"Column6", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column6]??0) <> 0),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Column2", "Column6", "Column5"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column2", "Type"}, {"Column6", "Amount"}, {"Column5", "Measure"}})
in
    #"Renamed Columns"

b) A single query to get all the tables formatted:

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "CD_")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Name", "Content"}),
    #"Transformed Tables" = Table.TransformColumns(#"Reordered Columns",{{"Content", TableToValues, type table}}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Transformed Tables", "Content", {"Type", "Amount", "Measure"}, {"Type", "Amount", "Measure"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Content",{{"Type", type text}, {"Amount", Int64.Type}, {"Measure", type text}})
in
    #"Changed Type"

 

 

Those two could use some optimising, but I think they are good enough.

 

Your result will look like this:

Smauro_0-1656599074000.png

So, if you want it a bit better formatted, I would suggest using a pivot table afterwards as they tend to look better visually in such cases.

 

 

Cheers,




Feel free to connect with me:
LinkedIn

View solution in original post

5 REPLIES 5
Borsti
New Member

So I figured out how to merge the two queries and its basically working. So now I could reference this one query to create the several tables which contain only one catergory of data as it was before. But is there a way to automatically break the table created by the master query into smaler tables that only contain specified data?

The evaluation of the gathered data I have running behind all that at the moment kind of requires seperate tables as it is referrring to table name and column and also using subtotals of each category...

But I am open to any suggestions 😉

 

https://docs.google.com/spreadsheets/d/1Ey06u0DfmFHfZ9GzW4uCx1azlBk27DK2/edit?usp=sharing&ouid=10614... 

 

Cheers

Borsti
New Member

Hello Smauro,

 

I addapted your proposal to my file and I get same results, so its basically working.

The only thing that you did not consider, pobably because you were not reading everything in detail, is that I need to deferentiate between the categories which are named in in a cell outside the tables. Those cells have specific names always starting with Tilte_*tablename*.

So I tried to append this and its working, but if I refresh the query it just shows the formula, only if i click into the cell and press enter it shows the content

How would i avoid that?

Thanks in advance!!!

 

https://docs.google.com/spreadsheets/d/1XN0QboAx7mllnpHTp7BnLfozvbyJjBOH/edit?usp=sharing&ouid=10614... 

 

Cheers

Interesting,

 

A first solution would be to actually make it a column name, but since we assume that every cell like that has a name, then what you want is possible.

 

That should get you the names in a different query:

 

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Title_")),
    #"Table Title" = Table.TransformColumns(#"Filtered Rows",{{"Content", each _[Column1]{0}, type text}})
in
    #"Table Title"

 

 

Then, you can merge this with the main query (you would just need to remove the "=" in your last step and choose this column for the merge)

 

I believe you can take it from there with expanding - renaming etc.

 

-- Edit

Here are the steps for merging etc.

,
    #"Insert Text" = Table.TransformColumns(#"Umbenannte Spalten", {{"MetMed", each Text.Insert(_, 0, "Title_"), type text}}),
    #"Merged Queries" = Table.NestedJoin(#"Insert Text", {"MetMed"}, TableTitles, {"Name"}, "TableTitles", JoinKind.LeftOuter),
    #"Expanded TableTitles" = Table.ExpandTableColumn(#"Merged Queries", "TableTitles", {"Content"}, {"Actual Title"})
in
    #"Expanded TableTitles"

Where TableTitles is the name of the new query.

Cheers,




Feel free to connect with me:
LinkedIn

Borsti
New Member

Hello Smauro,

 

thanks for the very quick reply. Unfortunately I did not have enough time to test if your solution does the full job, but what I can se that its deinitely going in the right direction!!!

Let me do some trials tomorrow and I'll let you know how it worked out.

Thanks again!!

 

Cheers

Smauro
Solution Sage
Solution Sage

So, first let me say that I haven't read your message in detail.

 

Now, you think well that parameterising and creating a custom function is a good option.

But, you could also do everything in one.

 

Assuming that:

1. All tables are formatted as tables.

2. Any table that you wish to transform this way, can be easily identified by its name (e.g. starts with "CD_")

3. You wish to view the result in one sheet

 

then you can have:

a) A function to format the tables as you wish (named TableToValues -referenced in next query):

(tbl as table) as table =>
let
    #"Removed Columns" = Table.RemoveColumns(tbl,{"Column1", "Column3", "Column4"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Column2", type text}, {"Column5", type text}, {"Column6", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Column6]??0) <> 0),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Column2", "Column6", "Column5"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Column2", "Type"}, {"Column6", "Amount"}, {"Column5", "Measure"}})
in
    #"Renamed Columns"

b) A single query to get all the tables formatted:

let
    Source = Excel.CurrentWorkbook(),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "CD_")),
    #"Reordered Columns" = Table.ReorderColumns(#"Filtered Rows",{"Name", "Content"}),
    #"Transformed Tables" = Table.TransformColumns(#"Reordered Columns",{{"Content", TableToValues, type table}}),
    #"Expanded Content" = Table.ExpandTableColumn(#"Transformed Tables", "Content", {"Type", "Amount", "Measure"}, {"Type", "Amount", "Measure"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Content",{{"Type", type text}, {"Amount", Int64.Type}, {"Measure", type text}})
in
    #"Changed Type"

 

 

Those two could use some optimising, but I think they are good enough.

 

Your result will look like this:

Smauro_0-1656599074000.png

So, if you want it a bit better formatted, I would suggest using a pivot table afterwards as they tend to look better visually in such cases.

 

 

Cheers,




Feel free to connect with me:
LinkedIn

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors