Forum Discussion
Restarting Formulas at Specified Data Points
- Jun 24, 2021
Rich_100 Okay, let's give it a try. You can follow the applied steps in my file. No need to reverse engineer them. But, ignore the query "Table1". I forgot to delete it.
Step 1 was to create a separate table with just the Customer, Age and Gender.
Step 2 is to go back to the same source (Query "Table1 (2)". Remove some unwanted columns. Then merge the query from step 1 with the cleaned-up table in step 2. This will add the Age and Gender to each Customer record. Reorder columns and then, probably the most important step is to select the customer, age and gender columns and then select to "unpivot other columns", Then you get a long list of "records" from which you can filter out the date fields.
Now you can merge the "Attribute" and "Value" columns, separated by a colon. Close and load to a table, to create the output that you see in columns M:P.
Step 3 is to create pivot table from that table (i.e. the end result from Step 2) in order to give you the condensed view per customer.
As you noticed, PQ has quite a steep learning curve. But once you get over the first hurdles, you'll love it. Good luck!
Rich_100 Forgive me for challenging your reporting request, but perhaps you could consider a more condensed approach. Rather than creating a large, rather unstructured, list with sub totals of some kind, why not create one ore more reports on the bases of cleaned and unpivoted data. A rough example is included in the attached workbook. I chose to load an intermediate table into Excel, just to demonstrate. In reality, you would probably load it into the Data Model and work from there.
Currently, I am learning Power Query as I go, which is a fairly steep learning curve. I’ve tried to reverse engineer what you’ve done, and made some head way, but can’t seem to get my outputs to look as clean as yours. Would you mind detailing your steps please?
- Riny_van_EekelenJun 24, 2021Platinum Contributor
Rich_100 Okay, let's give it a try. You can follow the applied steps in my file. No need to reverse engineer them. But, ignore the query "Table1". I forgot to delete it.
Step 1 was to create a separate table with just the Customer, Age and Gender.
Step 2 is to go back to the same source (Query "Table1 (2)". Remove some unwanted columns. Then merge the query from step 1 with the cleaned-up table in step 2. This will add the Age and Gender to each Customer record. Reorder columns and then, probably the most important step is to select the customer, age and gender columns and then select to "unpivot other columns", Then you get a long list of "records" from which you can filter out the date fields.
Now you can merge the "Attribute" and "Value" columns, separated by a colon. Close and load to a table, to create the output that you see in columns M:P.
Step 3 is to create pivot table from that table (i.e. the end result from Step 2) in order to give you the condensed view per customer.
As you noticed, PQ has quite a steep learning curve. But once you get over the first hurdles, you'll love it. Good luck!
- Rich_100Jun 25, 2021Copper Contributor
Perfect, thank you Riny_van_Eekelen!
Apologies if this is a silly question, but how have you got the pivot table into the format where it is showing the age and gender on the same row, as I can only get it to display as seen in the attached document?
- Riny_van_EekelenJun 25, 2021Platinum Contributor
Rich_100 Click anywhere inside the pivot table. On the "Design" ribbon, select "Report layout". First select "Show in tabular form" and then "Repeat all item labels". Still in the Design tab, select the "Subtotals" button and check "Don't show subtotals".