Forum Discussion
Row comparison and replacing values
Hello all,
Appreciate your contribution to the below.
This is a sample, for there are other Work_Order_Types, but those are not saved in the DB for EOD lines.
I need to have the Work_Order_Type for the same Work_Order_Number be added for the EOD lines, as displayed for the JOB and CLS Type.
Another Work_Order_Number might have a different Work_Order_Type, like SV.
In the imported data, it will show for CLS and JOB lines.
I want to have the same Work_Order_Type for the EOD lines.
I hope I clearly explained.
Please help.
Hi Anonymous ,
You can try to use the fill down function in the power query. See below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIAAUMlHSUvfycgGa4Uq4Ms6uwTjEXU1d8FSCIJGsENCEYThRiALgozIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkOrderNumber = _t, LineType = _t, WorkOrderType = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkOrderNumber", Int64.Type}, {"LineType", type text}, {"WorkOrderType", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "WorkOrderType", "WorkOrderType - Copy"), #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"WorkOrderType - Copy", "WorkOrderTypeComplete"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","",null,Replacer.ReplaceValue,{"WorkOrderTypeComplete"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"WorkOrderTypeComplete"}) in #"Filled Down"If the problem is still not resolved, please point it out. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
Anonymous Not sure I understand. Perhaps the Fill down feature? Or create a new column where you do a group by and extract the correct value in Power Query?
Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi Greg_Deckler,
Thanks for getting back to me. I'll try to be more explicit in the question.
Imported data from SQL Server is as below:
WorkOrderNumber
LineType
WorkOrderType
2000001
JOB
W
2000001
CLS
W
2000001
EOD
2000002
JOB
S
2000002
CLS
S
2000002
EOD
What I need is the following:
WorkOrderNumber
LineType
WorkOrderType
WorkOrderTypeComplete
2000001
JOB
W
W
2000001
CLS
W
W
2000001
EOD
W
2000002
JOB
S
S
2000002
CLS
S
S
2000002
EOD
S
Work Order has a WorkOrderType (W/S) for each WorkOrder.
Each WorkOrderNumber has 3 lines of data (JOB/CLS and EOD)
WorkOrderType for EOD is not stored in SQL Server DB.
I need a new column that adds the WorkOrderType for all LineTypes.
Trust this makes more sense. Thank you.
Hi Anonymous ,
You can try to use the fill down function in the power query. See below:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIAAUMlHSUvfycgGa4Uq4Ms6uwTjEXU1d8FSCIJGsENCEYThRiALgozIBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [WorkOrderNumber = _t, LineType = _t, WorkOrderType = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"WorkOrderNumber", Int64.Type}, {"LineType", type text}, {"WorkOrderType", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "WorkOrderType", "WorkOrderType - Copy"), #"Renamed Columns" = Table.RenameColumns(#"Duplicated Column",{{"WorkOrderType - Copy", "WorkOrderTypeComplete"}}), #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns","",null,Replacer.ReplaceValue,{"WorkOrderTypeComplete"}), #"Filled Down" = Table.FillDown(#"Replaced Value",{"WorkOrderTypeComplete"}) in #"Filled Down"If the problem is still not resolved, please point it out. Looking forward to your reply.
Best Regards,
Henry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
If you want to match values from another table, please refer to DAX - “CONTAINSX” – Finding if a value in table 1 has a matching value in table 2 - P3 Adaptive.
If not, please provide a more detailed explanation and give the expected result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
