Forum Discussion

JoeConradie's avatar
JoeConradie
Regular Visitor
5 years ago
Solved

Add table column that is filtered using a list in first table

Good day everyone
I need some assistance in the query editor as I don't know how to get around the problem and couldn't find any posts on the Power BI Community that could guide me in the right direction.

My scenario is as follows (simplified as much as possible):
I have the following table - 

IdNameValueFilter Lists
a1A1List
a2B2List
a3C5List
a4D4List
a5E1List
a6F8List
a7G3List
a8H0List
a9I5List
a10J2List
a11K2List

 

Each of the Lists in the column [Filter Lists] contains a list of Names that need to be filtered for. (e.g. for record a3 the list will be {"A","B"} and for record a5 the list will be {"A","C","D"}).
What I want to achieve is to add a column to this table that contains the current table up to this point filtered for the Names contained in the list for the specific record. Therefore if we take the two examples of a3 and a5 above, the added column for these two records will contain the following tables respectively:

 

a3 - 

IdNameValueFilter Lists
a1A1List
a2B2List

 

a5 - 

IdNameValueFilter Lists
a1A1List
a3C5List
a4D4List

 

I was able to add a column that contains the filtered table but it didn't filter differently for each record according to the list in the record.

 

Can someone please assist me with this? AlB you assisted me greatly the previous time and I would appreciate it if you can assist me again.
Thank you very much in advance for who ever is able to solve this 🙂

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY65EcAgDMB2cU2BISSkzP+NwFFk/yWinAsK3UmFn1LkVXEygUp1ZEBnCJYRXSBZdugKnWVCtzbboztkywE9IFpm9ARvOaJX26wev9th/f96rOsH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Name", type text}, {"Value", Int64.Type}}),
    ac = Table.AddColumn(#"Changed Type", "Filter", each if [Id]="a3" then {"A", "B"} else {"A","C","D"}),
    #"Added Custom" = Table.AddColumn(ac, "tables", each Table.FromRecords(List.Transform([Filter], (f)=> ac{[Name=f]})))
in
    #"Added Custom"

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY65EcAgDMB2cU2BISSkzP+NwFFk/yWinAsK3UmFn1LkVXEygUp1ZEBnCJYRXSBZdugKnWVCtzbboztkywE9IFpm9ARvOaJX26wev9th/f96rOsH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Name", type text}, {"Value", Int64.Type}}),
    ac = Table.AddColumn(#"Changed Type", "Filter", each if [Id]="a3" then {"A", "B"} else {"A","C","D"}),
    #"Added Custom" = Table.AddColumn(ac, "tables", each Table.FromRecords(List.Transform([Filter], (f)=> ac{[Name=f]})))
in
    #"Added Custom"
    • JoeConradie's avatar
      JoeConradie
      Regular Visitor
      5 years ago

      Hi Anonymous 

      Thank you so much for your assistance here. This worked just as I wanted it to work and I was able to apply this to my actual problem. Thank you so much for your speedy assistance. 🙂

      Have a blessed weekend.

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello JoeConradie 

     

    try this appraoch. Add a Index-column and then filter for this column, refering to the row index value

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZY45DoAwDAT/4joFObhKINz8IEpBSQ3/F+tylWJkzchKnJLcVowMQOf1vJ9kg+pgI3BUPWwCNdUAiyBQ1Z25eLeBLaCj2sJW4KnqzgYqqj1sL26wunQUB1v9/aScfw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Id = _t, Name = _t, Value = _t, #"Filter Lists" = _t]),
    ChType = Table.TransformColumnTypes(Source,{{"Id", type text}, {"Name", type text}, {"Value", Int64.Type}, {"Filter Lists", type text}}),
    AddIndex = Table.AddIndexColumn(ChType, "Index", 1, 1),
    AddColumnWithTableUntilThisRow = Table.AddColumn
    (
        AddIndex,
        "Filtered table",
        (add)=> Table.SelectRows(AddIndex, each [Index]<add[Index])
    )
in
    AddColumnWithTableUntilThisRow

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • JoeConradie's avatar
      JoeConradie
      Regular Visitor
      5 years ago

      Hi Jimmy801 

      Thanks for your assistance as well. This wasn't exactly what I wanted to achieve. The table that should be added in the new column should filter for a specific list that is different for each record. E.g. for one record I would want to filter for A and B and for a next record I would want to filter for A, C and D etc.

      Have a good weekend.