Identifying rows of data based on a single value on one row only.

[hixkim]

Hello! I am using PO data with PO numbers, line numbers, and part numbers (there are many more columns but they are not relevant). I would like to identify an entire PO and all associated PO lines based on a single part number within that PO. The other PO lines will have different part numbers but I still need them identified based on a value in another PO line.

There are only 2 part numbers that I need to identify so I can store these within the actual formula or create a separate table to store them in. I like the table idea in case the part numbers grow or shrink but I am open to any solution. 

I am trying to accomplish this within Power Query in Power BI. I have no idea where to start on this one. I really appreciate any help at all. 

Thank you!!

Example:

PO Number	Part Number	Line Number	Expected Result
11111	123	1	PART 123
11111	456	2	PART 123
11111	789	3	PART 123
11111	012	4	PART 123
11111	345	5	PART 123
22222	123	1	PART 123
22222	456	2	PART 123
22222	789	3	PART 123
33333	456	1
33333	789	2
33333	012	3
33333	345	4

[watkinnc]

Sorry, you can use the Add Columns and FillDown options right from the GUI.

--Nate

[hixkim]

Thanks Nate. That seems to be filling down the item number from Line 1 only. I am looking for it to fill in 2 specific part numbers only (600121 or 600122) whenever one of them exists on one of the order lines. They can exist on any order line or none at all. 

[watkinnc]

This works:

PartsColumn = Table.AddColumn(PreviousStepName, "Part_Numbers", each if [Line Number] = 1 then [Part Number] else null, type text),

Fill = Table.FillDown(PartsColumn, {"Part_Numbers"})

in

Fill

--Nate

[hixkim]

I'm sorry to even ask but could you show me how you would enter this? Maybe I would have been better off posting in the regular Power BI forum, I'm a little out of my depth. I've tried entering this in a few places and have tried googling how to enter this kind of statement but I am having a ton of luck. 

Also, in my sample data the part number happened to be on the first order line but that's not always (or even often) the case. Would that statement only work if the part number was listed on the first line?

Thanks for your help. 

[Jakinta]

I am not quite sure what do you want exactly...Maybe this can help...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY7BDcAwCAN34Z1HgdC0s0TZf41CUdK4fvhxsmX3ThyiQiwaTqN8rNrpLsDadbsrsIMl0sC0mru9TEK/jcn2jcn2DQ2tHAPLnADLL9jNL/5vPA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO Number" = _t, #"Part Number" = _t, #"Line Number" = _t]),
    Grouped = Table.Group(Source, {"PO Number"}, {{"GR", each if List.Contains (_ [Part Number], "123") then Table.AddColumn(_,"Expected", each "PART 123") else Table.AddColumn(_,"Expected", each "") }}),
    #"Expanded GR" = Table.ExpandTableColumn(Grouped, "GR", {"Part Number", "Line Number", "Expected"})
in
    #"Expanded GR"

[hixkim]

Sorry that's going over my head a bit. I have an excel data source with 45k lines. There are thousands of orders and each order has at least one order line. I want to evaluate each order to see if it has a specific part number (there are only 2 part numbers I care about, I need to check the orders for either, no order would have both). If it has that specific part number I would like a text value related to that part number to appear for each line of the order. I'm looking for the formula I could use to create that custom text column. 

I don't know if this explanation helped or made it worse. I really appreciate the effort. 

[Jakinta]

Try this with 2 text parameters for Parts. You have to create them and use in query below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY67EcAwCEN3oXZhfvnM4vP+awSC7ZioUPFOCLUG6IICSOwOvXxM9DCnxM7rNufEKpKnE2NRc30ZuX4/Jtt/TLb/YNfKYWKRo8RiS76NLbFPxEMVV9kAtJoG4KjpDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"PO Number" = _t, #"Part Number" = _t, #"Line Number" = _t]),
    Grouped = Table.Group(Source, {"PO Number"}, {{"GR", each if List.Contains(_ [Part Number], Part1) then Table.AddColumn(_,"Expected", each "PART "& Part1) else if List.Contains(_ [Part Number], Part2) then Table.AddColumn(_,"Expected", each "PART "& Part2) else
    Table.AddColumn(_,"Expected", each "") }}),
    #"Expanded GR" = Table.ExpandTableColumn(Grouped, "GR", {"Part Number", "Line Number", "Expected"})
in
    #"Expanded GR"