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 ...
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,

Borsti's avatar
Borsti
New Member
4 years ago

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

 

Cheers

Smauro's avatar
Smauro
Solution Sage
4 years ago

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,