Data Transform question (Pivot?)

[WLou]

Hi all

For some reason the report i'm downling is constructing the lis in an odd way see below first section 

Usually is to unpivot but this seems to pivot it, I have tried but that only applies to column with value 

If there is a similar post please feel free to link in 

Thanks

Regards,

Wendy

[DataChant · Accepted 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.

1. Start with the source table.
2. 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.
3. In the NoBlanks query, apply a filter on the AA Trx Dimension ID column and remove empty values.
4. In the Blanks query, apply a filter on the AA Trx Dimension ID and keep only the empty values.
5. Remove the last two (and empty) columns of the Blanks query.
6. 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".
7. 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.
8. 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. 

[WLou]

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

[DataChant]

Hi @WLou,

I updated the instructions in my original reply. Hope it is clear now.

You can download the solution file in here:

Solution PBIX File 

[WLou]

Thanks so much @DataChant 

I was stucking at creating the index and Pivot