Forum Discussion

Borsti's avatar
Borsti
New Member
4 years ago
Solved

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=106147662026345697416&rtpof=true&sd=true 

 

Cheers

 

 

Need Help
  • Smauro's avatar
    Smauro
    4 years ago

    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:

    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,