SOLVED

Merging Rows in Power Query

Copper Contributor
Hi,
 
Need to learn how to merge a row in data table when a record is broken into two rows
 
Also the record is in the incorrect column.
 
There are a number of more columns so the transpose option is not feasible
 
I have attached the excel file for the problem.
 
Kindly look at it and suggest a solution.
 
Regards,
Sumair
28 Replies

@SumairChughtai Would be helpful if you could explain how you would wan the end result to look like.

@SumairChughtai

 

When you say:

Need to learn how to merge a row in data table when a record is broken into two rows

I don't see that in your sample file

(looking at Transaction number and Reference fields/columns),

so in what context does the above statement refer to?

 

 cheers

@Riny_van_Eekelen 

I would like to merge them as in the attached excel file.

The red font indicates what the data is in its original form.

The yellow highlighted is how I want the data to end up as one complete record.

Thank you for your interest in the problem.

Regards,

Sumair

 

@Yea_So 

I would like to merge them as in the attached excel file.

The red font indicates what the data is in its original form.

The yellow highlighted is how I want the data to end up as one complete record.

Thank you for your interest in the problem.

Regards,

Sumair

 

@SumairChughtai 

 

So you're basically saying the report looks like this:

Yea_So_0-1627116766269.png

 

am I understanding it correctly?

best response confirmed by allyreckerman (Microsoft)
Solution

@SumairChughtai Perhaps like in the attached file. I assumed that the raw looks like in the blue table. The Power Query output is in the green table, using only standard UI commands.

@Riny_van_Eekelen 

 

I don't like to work on a solution based on a guess premise, its a time waster, I'd rather be watching movies on netflix if the challenge is based on a guess.

@Riny_van_Eekelen 

 

it kind of cracks me up this other user asking for help on a different thread, does not want to create at least a 20 to 40 rows of sample data, then the truth comes out ... turns out he's got 7,000 rows of data to organize. lol

@SumairChughtai 

The kind of issue that definitively requires > 1 example... Put together something (attached) with what you provided + added a couple of extra broken records

 

NB: When a [Transaction Number] is broken as 2 records (what you gave us with: when a record is broken into two rows)
- The 2 records must be consecutive in the Source Table/file (otherwise I don't see how you're going to resolve this...)
- The 2nd partial record is "added" to the 1st available location in the previous record

Hello Riny,

Your second table in the attached excel file (the one in green) is how I would like my format to be in Power Query. Kindly enlighten me as to how you came up with this solution.
Thank you!

@L z. That is exactly how I like the data to be. Kindly share how you were able to get to this form in Power Query.

Thank You!

"The 2 records must be consecutive in the Source Table/file (otherwise I don't see how you're going to resolve this...)"

Yes. These are two consecutive records in the master data file that I'm working on.

"The 2nd partial record is "added" to the 1st available location in the previous record"

Kindly educate me on how to achieve that in Power Query

@Yea_So Thank you for your suggestion. But this is not how I would like the data to be.

 

The solution that Riny and L.z have come up with is what I'm after.

 

Perhaps I wasn't able to define the problem correctly.

 

Thank you for your input.

 

@SumairChughtai 

@Riny_van_Eekelen used the following table structure as input/Source:

RynyInputTable.png

 

and I used a different one:

LzInputTable.png

At the end we get what you expect BUT the approaches (while similar) are necessarily different

 

So could you please clarify 2 things:

- What's the exact structure of your input/Source table? Riny's one? Mine or something else?
Could you post a picture (a few rows only) of you actual input/Source table to clarify things

- What do you exactly have in mind when you say "Kindly educate me on how to achieve that in Power Query"?

 

Thanks

 

 

@L z. 

IMHO, key question here are there any rules and logic in filling source data. If not, screenshot could illustrate current state, but next day another person adds data in different way - any solution stops working.

100% agree Sergei hence why I added a couple of dummy records to my sample to check the logic I implemented could handle a few other scenarios than the only one the OP exposed. Realistically speaking I don't think we can do more - at that stage - than providing some possible approaches as it seems already difficult enough to get an accurate baseline (structure of the Source table)

@L z. @Sergei Baklan @Riny_van_Eekelen 

The data is generated by my Company's source system. There's no manual or anyother kind of intervention.

 

Obviously, I can't share more than what I have. However, it serves the purpose of defining the problem.

 

The output from the source system sometimes breaks records into two rows (as in the excel file attached), with values in incorrect columns as well.

 

Since there is a significant amount of data, I require a solution via Power Query to merge such rows and create a single record, for each transaction, with the values in the correct columns.

 

@L z. Your solution is what I'm looking for, it created a single record with all values in the correct columns.

 

@L z. @Riny_van_Eekelen Riny's solution (in the green table) merged the two rows together into a single record, with all values in the correct columns.

 

Requesting how to achieve your results in Power Query.

 

Regards,

Sumair

@L z. 

SumairChughtai_0-1627149127750.png

The structure you shared is the correct one. This structure defines my problem

 

1) Row 3 is broken into Row 3 and Row 4

 

2) The highlighted bit should be in Row 3 in the same order starting after CP Long Name i.e. after PQRST

 

I want to learn how to merge these two rows together such that all the values appear in the correct columns.

 

Regards,

Sumair

@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:

Yea_So_0-1627155957613.png

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: Part 3: Programming "For-Next"-Loops using List.Accumulate() in M for Power Query - data-insights.de...

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

SUMIF between 2 tables using Power Query - YouTube

 

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

 

 

 

1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@SumairChughtai Perhaps like in the attached file. I assumed that the raw looks like in the blue table. The Power Query output is in the green table, using only standard UI commands.

View solution in original post