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.
Hi,
I have decided to move my topic to the Power Query section to seek your further help.
I've put the power BI example here: https://dropmeafile.com/#559874d471
I have 2 tables which are not connected to each other:
- Table with Users and Conditions they meet
- Criteria with different sets of values; they define, when certain criteria is met - all of values need to be met to create a certain criterium
I need to make a calculation in power query which will search for all those sets of values through all users and create a table listing out the users with all criterias they meet. I will show this static table in power BI.
I have so much data that I cannot due it with a measure - takes to long to refresh because it recalculates all the time.
Thanks for support,
Marek
Solved! Go to Solution.
Hi @Marek12345
You can create refer to the following queries.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci7KLEktykxUMFTSUXKGkkZg0hRMminF6iApM8JQZgxVjKLMGEOZOZBEVWOCpMYYaheItFCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Criteria = _t, #"Column 1" = _t, #"Column 2" = _t, #"Column 3" = _t, #"Column 4" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Criteria", type text}, {"Column 1", type text}, {"Column 2", type text}, {"Column 3", type text}, {"Column 4", type text}}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Criteria"}, "Attribute", "Value"),
#"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each [Value] <> null and [Value] <> ""),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Combine(Table.SelectRows(#"Filtered Rows",(x)=>x[Criteria]=[Criteria])[Value],",")),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Attribute", "Value"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
#"Added Custom1" = Table.AddColumn(#"Removed Duplicates", "Custom.1", each 1)
in
#"Added Custom1"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi1OLVIwVNJRcgaSsTooAkboAsboAiboAqboAmYIASN0W4zQbTFCN8MI3QxjdGtN0A01QTfUBN3pJuiGQgQslGJjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Users = _t, Conditions = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Users", type text}, {"Conditions", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each 1),
#"Merged Queries" = Table.NestedJoin(#"Added Custom", {"Custom"}, Query2, {"Custom.1"}, "Query2", JoinKind.LeftOuter),
#"Expanded Query2" = Table.ExpandTableColumn(#"Merged Queries", "Query2", {"Criteria", "Custom"}, {"Criteria", "Custom.1"}),
#"Added Custom1" = Table.AddColumn(#"Expanded Query2", "Custom.2", each let a=Text.Split([Custom.1],","),
b=List.Count(a),
c=List.Distinct(Table.SelectRows(#"Expanded Query2",(x)=>x[Users]=[Users])[Conditions]),
d=List.Count(List.Intersect({a,c}))
in if b=d then "Yes" else "No"),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Conditions", "Custom", "Custom.1"}),
#"Removed Duplicates" = Table.Distinct(#"Removed Columns"),
#"Pivoted Column" = Table.Pivot(#"Removed Duplicates", List.Distinct(#"Removed Duplicates"[Criteria]), "Criteria", "Custom.2")
in
#"Pivoted Column"
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
DAX Table:
M Query:
Hi, this works like a charm! Thank you!
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.