Forum Discussion
Data Transform question (Pivot?)
Hi WLou,
To confirm - Are you looking to transform the From table to the To table? Applying a Pivot will not work, because the Pivot step will treat the missing values under "AA Trx Dimension ID" column as a new blank column. To get to the desired format, you would need to follow these steps.
- Start with the source table.
- Create two reference queries from the source query by right-clicking on the source query and selecting Reference. Repeat it twice, and rename the new queries NoBlanks and Blanks.
- In the NoBlanks query, apply a filter on the AA Trx Dimension ID column and remove empty values.
- In the Blanks query, apply a filter on the AA Trx Dimension ID and keep only the empty values.
- Remove the last two (and empty) columns of the Blanks query.
- If you don't have a unique key in your data, you would need to add to the NoBlanks query an Index column and then apply Divide-Integer on it to create a unique index for the multi-line records (See attached solution file in my last response). I also cover this technique in detail in Chapter 7 of my book "Collect, Combine & Transfer Data using Power Query in Excel and Power BI".
- Apply Pivot Column on the AA Trx Dimension ID column of the NoBlanks query. In the advanced options of the Pivot Column, select Don't aggregate.
- Apply Append Queries As New on the Blanks and NoBlanks.
If you are not sure how to follow these instructions, please send me a sample report and I will attach the solution.
Hi WLou,
To confirm - Are you looking to transform the From table to the To table? Applying a Pivot will not work, because the Pivot step will treat the missing values under "AA Trx Dimension ID" column as a new blank column. To get to the desired format, you would need to follow these steps.
- Start with the source table.
- Create two reference queries from the source query by right-clicking on the source query and selecting Reference. Repeat it twice, and rename the new queries NoBlanks and Blanks.
- In the NoBlanks query, apply a filter on the AA Trx Dimension ID column and remove empty values.
- In the Blanks query, apply a filter on the AA Trx Dimension ID and keep only the empty values.
- Remove the last two (and empty) columns of the Blanks query.
- If you don't have a unique key in your data, you would need to add to the NoBlanks query an Index column and then apply Divide-Integer on it to create a unique index for the multi-line records (See attached solution file in my last response). I also cover this technique in detail in Chapter 7 of my book "Collect, Combine & Transfer Data using Power Query in Excel and Power BI".
- Apply Pivot Column on the AA Trx Dimension ID column of the NoBlanks query. In the advanced options of the Pivot Column, select Don't aggregate.
- Apply Append Queries As New on the Blanks and NoBlanks.
If you are not sure how to follow these instructions, please send me a sample report and I will attach the solution.
Hi DataChant
Sorry I got stuck at the very last 2 steps I must have missed points of the reason of having reference table
I have attached a sample for you below please let me know if that's good enough
Audit Trail Code Vendor ID AA Debit Amount AA Credit Amount AA Assigned Percent AA Distribution Reference AA Trx Dimension ID AA Trx Dimension Code PMTRN00002609 AMBMA01 175.60000 0.00000 100.00000% PMTRN00002609 ANAIS01 339.00000 0.00000 100.00000% PMTRN00002609 PHILO01 106.64000 0.00000 100.00000% PMTRN00002610 DYLHO01 2,130.91000 0.00000 50.00000% Sample Text 1 CLIENT MANG01 PMTRN00002610 DYLHO01 2,130.91000 0.00000 50.00000% Sample Text 1 FUND SOURCE TCP-PRO-01 PMTRN00002610 DYLHO01 2,130.91000 0.00000 50.00000% Sample Text 1 CLIENT MANG02 PMTRN00002610 DYLHO01 2,130.91000 0.00000 50.00000% Sample Text 1 FUND SOURCE TCP-PRO-01 PMTRN00002610 KICDR01 383.35000 0.00000 100.00000% Sample Text 2 CLIENT KODI01 PMTRN00002610 KICDR01 383.35000 0.00000 100.00000% Sample Text 2 FUND SOURCE DHS-PCFA-01 PMTRN00002610 VICBS01 260.00000 0.00000 100.00000% Sample Text 3 CLIENT MAGO03 PMTRN00002610 VICBS01 260.00000 0.00000 100.00000% Sample Text 3 FUND SOURCE DHS-PCFA-01 PMTRN00002657 VICBS01 144.99000 0.00000 33.00000% Sample Text 6 CLIENT MAGO02 PMTRN00002657 VICBS01 144.99000 0.00000 33.00000% Sample Text 6 FUND SOURCE DHS-PCFA-01 PMTRN00002657 VICBS01 144.99000 0.00000 33.00000% Sample Text 6 CLIENT MAGO03 PMTRN00002657 VICBS01 144.99000 0.00000 33.00000% Sample Text 6 FUND SOURCE DHS-PCFA-01 PMTRN00002657 VICBS01 145.02000 0.00000 33.00000% Sample Text 6 CLIENT MAGO04 PMTRN00002657 VICBS01 145.02000 0.00000 33.00000% Sample Text 6 FUND SOURCE DHS-PCFA-01 Hi WLou,
I updated the instructions in my original reply. Hope it is clear now.
You can download the solution file in here:
