Merging Rows in Power Query

SumairChughtai 

I have not made any suggestions yet, I am still trying to clarify what the source data structure/geography with you so to clarify:

1. The above image is what your source data looks like "Consistently"

The expected answer from you is either a yes consistently or no it is inconsistent.

If the answer is the former and not the later, then the approach would be a

Option 1

1. loop function in PQ evaluating each row, where the Transaction number can be had for the 2nd row for all row/records in the second row, 

2. Do a  Table.SelectRows() on the main table to create 2 tables.

first Table.SelectRows() will be the table for the first row.

second TableSelectRows() will the the table for the 2nd row

3. Do a merge query using the Transaction number to match all rows for the table on the right and table on the left

4. Remove the excess columns then rename the columns for the second table.

References:

PQ For Loops: https://www.data-insights.de/part-3-for-next-loop-using-list-accumulate-in-m-for-power-query/

Example for PQ evaluating rows Using IF, AND, OR  and TableSelectRows()

https://www.youtube.com/watch?v=twgcfuJx1sU

Option 2

1. Would be to put formulas on the 2nd row referencing the Transaction Number on the first row on the source table,

2. Do a PQ on the source table the source table

3. Create a reference copy of the PQ on the source table

4. Filter out the 2nd row on the PQ to the source table

5. Filter out the 1st row on the reference table of the PQ to the source table

6. Do a merge query on PQ of source table and the reference table.

Option 3 Data Model:

1. Would be to put formulas on the 2nd row referencing the Transaction Number on the first row on the source table,

2. Do a PQ on the source table the source table

3. Create a reference copy of the PQ on the source table

4. Filter out the 2nd row on the PQ to the source table

5. Filter out the 1st row on the reference table of the PQ to the source table

6. Add to the data model

7. Create Relationships

8. Add columns to table 2 using the DAX RELATED() FUNCTION

9. Create a Power Pivot 

cheers