Flatten a table to a list but keep a link to the original data

[teastreet]

I have a table of data with multiple rows and columns (100 columns, 400 rows).

To enable me to report on this data I needed to flatten this table to a list format. I have done this no problem using the pivot table wizard and I'm happy with the results.

My problem now is that I have two large tables of data that that need to be updated. The original table of data with multiple rows and columns and the flattened list that has come out of the pivot table.

It is not feasible to update both tables manually nor run the pivot wizard each time there is an update to the original table, which is constant. I need to keep both the original table and flattened list maintained.

So my question: is there an efficient way to link the original data to the flattened list so that when the original data is updated the flattened list will also be updated?

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[teastreet]

Hi thanks for the reply. I have attached a file with sample data (the actual spreadsheet has hundreds of rows and columns).

The worksheets have been labelled as follows:


(1) Master Data sheet (data is named as Table1).

(2) Pivot Table used to flatten the Master Data.

(3) Flattened data from the Pivot Table. There is an IF statement in column 4 which I've added just to give a value based upon column 3.



The values in the Master Data sheet (1) are updated frequently and at the moment I am really struggle to keep both (1) and (3) maintained manually. Constantly updating the Pivot Table (2) is not feasible either as I have that additional column in (3) with the IF, so I need to redo it everytime, even for the smallest update.


Ultimately what I want to do is find a way to automate updates. For example, when I update a value in the Master Data (1) I want this to be reflected in the Flattened Data table (3).

[alansidman]

Using Get and Transform found on the Data Tab of your Excel file, you can get the data into the format you require by loading the original table to the PQ editor and unpivoting the columns. Here is the Mcode to do that. When new data is added to the original table. Click on the Refresh all Icon on the Data tab. Go to the output sheet and the table is updated.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"DATA1", type any}, {"DATA2", type any}, {"DATA3", type any}, {"DATA4", type any}, {"DATA5", type any}, {"DATA6", type any}, {"DATA7", type any}, {"DATA8", type any}, {"DATA9", type any}, {"DATA10", type any}, {"DATA11", type any}, {"DATA12", type any}, {"DATA13", type any}, {"DATA14", type any}, {"DATA15", type any}, {"DATA16", type any}, {"DATA17", type any}, {"DATA18", type any}, {"DATA19", type any}, {"DATA20", type any}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps

M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced

[teastreet]

Thanks so much for the reply, this is exactly what I needed! I was not aware of this feature in excel - it's really powerful. I've just about got this to work for my use case, with a bit of fine tuning needed:

• Is it possible to somehow include the IF statement (as I have in the sample xlsx file worksheet 3, column D) into the PQ report so that it automatically populates a column? Or would this still need to be bolted on in the worksheet as it is now.
  
• Is it possible to pick and choose which columns are brought into the PQ? I actually have ~100 columns defined in my table and only really need to bring in about half of them to pivot. The sample data was nice and clean and did not include these other columns and in my real data I am getting a lot of data brought in I don't need. I tried only having the ones I need in the LET string but it didn't make any difference.
  I've attached an updated sample file which should make this clearer.
  
• Finally, does the PQ ignore blank cells?

[alansidman]

You can add an If statement by adding a new column and then creating an If then statement.
You cannot choose which columns to bring in as it brings in a table, however, once in PQ, you can select which columns to keep and which to remove.
As to blank cells, PQ treats those as null.

New Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Column1", "DATA1", "DATA2", "DATA3", "DATA4", "DATA5", "DATA6", "DATA7", "DATA8", "DATA9", "DATA10", "DATA11", "DATA12", "DATA13", "DATA14", "DATA15", "DATA16", "DATA17", "DATA18", "DATA19", "DATA20"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Other Columns", {"Column1"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Custom", each if [Value]=1  then "Open" else if [Value] = 0 then "Closed" else "N/A")
in
    #"Added Custom"

If you wish to learn more about PQ, then pick up the book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar. Available on Amazon.

[teastreet]

Just so I've understood, PQ will bring in all the columns defined in the table, in this case 'Table1'.

Then on the step #"Removed Other Columns" you have only included the columns you want to see in the report i.e. only the DATAx columns and omitted the columns OTHERDATAx?


That's really useful to add the IF statement - is it also possible to add other Excel formulas in the same way? In particular a VLOOKUP?

Thank you for your help.

Thanks also for the book recommendation - I have ordered it. This feature is something I really want to learn more about.

[alansidman]

there is no Vlookup function, but what you can do is merge (join) two tables on a particular field (common value) and this does the same as Vlookup except it brings back all matches not just the first one.

Your first assumption is correct.