Combine multiple row fields in Power Query based on multiple other field conditions

[JamieAU1980]

Hi 

I've had a search of the forums, but couldn't find the answer to my question; some touched on the subject, but not to the level of complexity that I'm dealing with.

I am trying to combine the contents of multiple rows in a table, based on the values in other fields.

To give the background, our ERP system splits purchase order line descriptions (potentially multiple) & charge lines in an order table. In Power Query, I am trying to consolidate the description lines and the charge lines, so that when I create a table in Power BI, I can just add the charge line to the table and it will include the relevant description lines. This makes for a more concise table.

I have created a forumla in Excel that does what I'm after, I just can't replicate in Power Query. The excel formula I have written is:

=IF(D2="SN",G2,IF(A2&B2=A1&B1,IF(D2="DN",IF(D1="DN",I1&" "&G2,G2),I1),G2))

To summarise in words, if the po_order_no & backorder_flag is the same as the row above, if the po_line_type equals "SS" (which is the charge line), then it combines all the "DN" lines from above the "SS" line type. The "SN" if statement at the front, is just a statement to handle if the line type is a Stock Order line, which is to be excluded in the line description combination. 

The following is the excel based desired result, where I have filtered out the "DN" type note rows, and it then provides a concise table with all the info I need.

I appreciate in advance any help you can provide.

Thanks

Jamie

[edhans]

One method you can try is this:

1. Create your base query and don't load it.
2. Add two indexes, one starting at 0, one starting at one.
3. Create a references to that query, then merge again with that query, using the 1 index first to the 0 index. Expand only the columns you need.

You will get this:

now you can create a formula that only deals with one row, because each row has data from the previous row in it.

This will work well for a few thousand records, maybe more. I question its performance if you have millions of records.

You can see this in the attached Excel file. If there are millions of records, try it, but if it is too slow, post back. There is a far more complex solution that will work.

[JamieAU1980]

Hi

Sorry, it hasn't achieved what I'm after; or in the least, I'm unable to modify it for my purposes.

The "Desired Description Result" column isn't source data (although I had it in my table), it is purely what the correct combination of text that should look like when using the "Line Desc" field as the source to be combined.

I'm not sure how the index it is handling the fact that there are potentially multiple "DN" note types above an "SS" note type, that I am then trying to combine all the text (ideally with a space between lines) on the SS note type (assuming of course the PO NO. & Backorder are the same).

Hopefully the following image helps to clarify the desired result.

Thanks for your help

Jamie

[edhans]

That is a DAX solution, not Power Query. I'd use CONTATENATEX() for the measure, filtering on the current PO number, the lines that are above the line number SS is on, then concatenate the values in the Line Desc. field.

I'd need data to play with though. A clean excel file shared via OneDrive or Dropbox. I or someone else could take a crack at it.

Power Query is not designed to do column scans like you are wanting. DAX is ideally suited for that.

[JamieAU1980]

Hi

Hopefully this file shares ok.

https://www.dropbox.com/scl/fi/wp334z37m9ly6cfjfew2d/Combined-special-order-lines-For-Onedrive.xlsx?...

The SN type lines are ok as is, and don't need to be combined with any other lines, as for these items they have a description on the PO line itself. 

I should point out that both the "PO NO." & "Backorder" fields need to match (the line above) for the line to be valid for joining the line descriptios. I can bring in the table sorted, but essentially the line descriptions are joined on numerical sequence of "Line Seq", "DN" types down separated by a space, until a "SS" type is reached where we then want the combined descirption.

I hope this all makes sense, let me know if it doesn't

Cheers

[JamieAU1980]

Ok, I'll get a dropbox or Onedrive Account sorted and post when done.

Thanks

[edhans]

@JamieAU1980 - was the above helpful at all?

[edhans]

You didn't give us any data to work with, so I just used your Excel formula and tried to break it down. this is untested.

=IF(
	D2="SN",
	G2,
	IF(
		A2&B2=A1&B1,
			IF(
				D2="DN",
				IF(
					D1="DN",
					I1&" "&G2,
					G2
				),
			I1),
		G2)
	)
POWER QUERY EQUIVALENT
if D2 = "SN"
	then G2
	else if
		A2&B2 = A1&B1
			then (
				if D2 = "DN"
					then (
						if D1 = "DN"
							then I1&" "&G2
							else G2
						)
					else I1
				)
		else g2

When nesting if/then/else in PQ, you want the nested if to be wrapped in parenthesis. So when it sees

...then if a-1 then b else c

It knows that entire statement is what "then" should report.

...then (if a-1 then b else c)

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[JamieAU1980]

Apologies, completely my fault, I didn't do the best job of explaining a crucial element.

So, for my Power BI model, I'm pulling the data through via an ODBC connection to the server, so what I am trying to do is replicate what I've done in Excel, but in a table existing in Power Query; so the same formula / cell references obviously will not work. I gather I'll need to use M to group or aggregate the rows based on conditions, but I have no idea how to do that to this extent.

Happy to share the source excel data, but not sure if that will help given what I'm trying to do is manipulate the power query table with M.

[edhans]

I understood that. You'd need to replace cell references with field names. So D2 becomes [PO Number] or whatever. No row reference needed. 

Share data in Excel, that is fine. We can do the work from there in Power Query, but have to have a good understanding of the desired result. You would then need to migrate the code to your model.

Info on what and how to share below.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[JamieAU1980]

Sample Table of data shared at the bottom.

For your Power Query equivalent, I understand the majority, however, how do I refer to the row above, as I have done in Excel?

Modifying your code to the actual power query table, I get the following (untested), I'm just not sure how to include the parts of the formula that are not in the same row (in my verision of your code, essentially those parts where I have left a cell ref instead of a table field value).

I'll obviously need to change the code somewhat to also allow for the fact the my excel formula starts in row 2, as row 1 is a header row.

POWER QUERY EQUIVALENT
if [Line Type] = "SN"
	then [Line Desc]
	else if
		[PO NO.]&[Backorder] = A1&B1
			then (
				if [Line Type] = "DN"
					then (
						if D1 = "DN"
							then I1&" "&[Line Desc]
							else [Line Desc]
						)
					else I1
				)
		else [Line Desc]

PO NO.	Backorder	Line NO.	Line Type	Stock Code	Supplier Part NO.	Line Desc	Item Price	Desired Description Result
159022		1	SN	38105E	2955709	SUSPENSION GROUP REAR	20000	SUSPENSION GROUP REAR
159022		3	DN			CORE CHARGE	0	CORE CHARGE
159022		4	DN			HYD CYLINDER		CORE CHARGE HYD CYLINDER
159022		5	SS	Special Item			9000	CORE CHARGE HYD CYLINDER
159022		7	DN			RETURN CORE	0	RETURN CORE
159022		8	SS	Special Item			0	RETURN CORE
159022		9	DN				0
159022		10	SN	38105E	2955709	SUSPENSION GROUP REAR	20000	SUSPENSION GROUP REAR
159022		11	DN			CORE CHARGE	0	CORE CHARGE
159022		12	SS	Special Item			9000	CORE CHARGE
159022		13	DN			RETURN CORE	0	RETURN CORE
159022		14	SS	Special Item			0	RETURN CORE
159022		17	DN			AS PER QUOTE: 648959	0	AS PER QUOTE: 648959
159022	AA	7	DN			RETURN CORE	0	RETURN CORE
159022	AA	8	SS	Special Item			9000	RETURN CORE
159022	AA	9	DN				0
159022	AA	13	DN			RETURN CORE	0	RETURN CORE
159022	AA	13.5	DN			LIFT CYLINDER		RETURN CORE LIFT CYLINDER
159022	AA	14	SS	Special Item			9000	RETURN CORE LIFT CYLINDER
159022	AA	17	DN			AS PER QUOTE: 648959	0	AS PER QUOTE: 648959
159062		3	SN	51691E	374-2278X	CYLINDER GROUP DUAL TILT LH	8138.4	CYLINDER GROUP DUAL TILT LH
159062		4	DN			CORE CHARGE	0	CORE CHARGE