The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi,
I have a dataset with 2 families and their respective members.
This includes a family column and a person column.
I want a third column to return TRUE if Simon is member of the family.
Right now the conditional column I added in query only return TRUE on the row where Simon is, but I want it to return TRUE on the rows which contain the family of Simon.
Take a look at representation of my problem on the picture below.
How can I acheive the column on the right (in power query)?
PS: The coloring are just to make the picture understandable, and are not meant to be part of the solution in query.
All responses and suggestions are appreciated and welcomed.
Thank you,
Frode
Solved! Go to Solution.
The solution will display all your columns of previous step and will preserve all your previous steps. You just need to make use of these 2 steps after your previous step. You will need to replace #"Added Custom" with your previous step. This presumes that Is Simon Member column has already been added you.
List = List.Distinct(Table.SelectRows(#"Added Custom",each [Is Simon member]=true)[Family]),
Custom1 = Table.ReplaceValue(#"Added Custom",each [Is Simon member],each List.Contains(List,[Family]),Replacer.ReplaceValue,{"Is Simon member"})
See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQpILCnKTM5WitWB8IMzc/Pz4DyPxLy8xAw41zexqBLO8cksTgRzjECcxNKi1Dw41yM1LwVuLEjAKz8DIeudmJtYBNFrDOQWpaYWlcN55alFcDbUMbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Family = _t, Person = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Family", Int64.Type}, {"Person", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Is Simon member", each [Person]="Simon"),
List = List.Distinct(Table.SelectRows(#"Added Custom",each [Is Simon member]=true)[Family]),
Custom1 = Table.ReplaceValue(#"Added Custom",each [Is Simon member],each List.Contains(List,[Family]),Replacer.ReplaceValue,{"Is Simon member"})
in
Custom1
Hi,
Thank you for your response.
As this is just a presentation of my problem, and I want to integrate the solution in a complex corporate dataset with other column names and values, I cant paste the code directly in.
But from what I understand, there are no simple fix to this problem?
I cant solve this by using the add custom column or add conditional column functions?
Thank you,
Frode
The solution will display all your columns of previous step and will preserve all your previous steps. You just need to make use of these 2 steps after your previous step. You will need to replace #"Added Custom" with your previous step. This presumes that Is Simon Member column has already been added you.
List = List.Distinct(Table.SelectRows(#"Added Custom",each [Is Simon member]=true)[Family]),
Custom1 = Table.ReplaceValue(#"Added Custom",each [Is Simon member],each List.Contains(List,[Family]),Replacer.ReplaceValue,{"Is Simon member"})
Thanks, okay, I will try this next work day on monday, and come back here for further comments or solution acceptance 🙂
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
39 | |
20 | |
19 | |
14 | |
13 |