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.
My case is the following:
I have a dataset with many rows and columns. One column is the description which I want to update its data with a condition.
Example data:
In the 'New Description' Column, I am trying to have the value 'the description I want to update with' for the three rows with code AAA000 and the rows with code AAA111 to have as New Description 'Other Description' etc.
Any ideas?
Solved! Go to Solution.
Hi @Anonymous
You can add a conditional column first.
Then group by Code column and add two new columns as below.
Finally expand "All Data" column and select only old Description column.
Full code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0NDAwUNJRKslIVUhJLU4uyiwoyczPU/BUKE/MK1EoyVeK1UFSlp+TAlaGX9TQ0BAkCjSzCNlQNGk0TUZGRiia0MSxq4aLxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Description = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Description", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "New Description", each if Text.StartsWith([Description],"old") then null else [Description]),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Code"}, {{"New Description", each List.Max([New Description]), type nullable text}, {"All Data", each _, type table [Code=nullable text, Description=nullable text, New Description=nullable text]}}),
#"Expanded All Data" = Table.ExpandTableColumn(#"Grouped Rows", "All Data", {"Description"}, {"Description"})
in
#"Expanded All Data"
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
Desired output 👆
@Anonymous - try this calculated column measure
New Description = IF('Table (2)'[Code] = "AAA000", "the description I want to update with", "Other the Description").
Let me know if this resolve your query the MARK AS ACCEPTED SOLUTION. Many Thanks
Thank you for your answer.
However, I can't hardcode the description. In my real data, there are thousands of data that I need to update. I need an expression like this: for code 'AAA000', replace the description that starts with 'old' with the description that does not start with 'old'.
Hi @Anonymous
You can add a conditional column first.
Then group by Code column and add two new columns as below.
Finally expand "All Data" column and select only old Description column.
Full code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnR0NDAwUNJRKslIVUhJLU4uyiwoyczPU/BUKE/MK1EoyVeK1UFSlp+TAlaGX9TQ0BAkCjSzCNlQNGk0TUZGRiia0MSxq4aLxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Code = _t, Description = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Code", type text}, {"Description", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "New Description", each if Text.StartsWith([Description],"old") then null else [Description]),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Code"}, {{"New Description", each List.Max([New Description]), type nullable text}, {"All Data", each _, type table [Code=nullable text, Description=nullable text, New Description=nullable text]}}),
#"Expanded All Data" = Table.ExpandTableColumn(#"Grouped Rows", "All Data", {"Description"}, {"Description"})
in
#"Expanded All Data"
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!
I think this is what I am looking for. Thank you all, folks!
@Anonymous - not very clear about your requirement, but try this or tinker the DAX if required.
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.