Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
BATCH | PO |
1 | 5 |
# | 5 |
2 | 4 |
3 | 6 |
Hello,
I'm looking for the solution how to replace the value (#) in one column (BATCH) with the another value (1) from the same column based on duplicated value (5) in another column (PO).
Here is what i want to get:
BATCH | PO |
1 | 5 |
1 | 5 |
2 | 4 |
3 | 4 |
thank you in advance for any tip,
Maria
Solved! Go to Solution.
given that the AIB answer is correct, if you are better off with the PBI GUI, these are the steps you can follow to get the result:
order before column2 acsending, then column1 descending
finally, fill down column1
Hi @ShiMaria
Place this code in a blank query to see the steps. The #"Added Custom" step is the important one:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTJVitWJVlKGs4yALBMwyxjIMlOKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [BATCH = _t, PO = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"BATCH", type text}, {"PO", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [BATCH] = "#" then List.Max(Table.SelectRows(#"Changed Type", (inner)=> inner[PO]=[PO] and (inner)[BATCH]<>"#")[BATCH]) else [BATCH]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"BATCH"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "BATCH"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"BATCH", "PO"})
in
#"Reordered Columns"
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
may be I incert it wrongly, but it doesn't make any difference to my tables..
given that the AIB answer is correct, if you are better off with the PBI GUI, these are the steps you can follow to get the result:
order before column2 acsending, then column1 descending
finally, fill down column1
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |