Forum Discussion

Marek12345's avatar
Marek12345
Frequent Visitor
2 years ago
Solved

Create new table in Power Query with data that meet certain conditions from other tables

Hi, 

 

I have decided to move my topic to the Power Query section to seek your further help.

 

https://community.fabric.microsoft.com/t5/Power-Query/Filter-data-that-match-all-conditions-in-other-table-multiple/m-p/3738180/thread-id/122794#M122837

 

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

 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.