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.

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.