Forum Discussion

JoeConradie's avatar
JoeConradie
Regular Visitor
6 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 dire...
Need Help
  • Anonymous's avatar
    Anonymous
    6 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"
Jimmy801's avatar
Jimmy801
Community Champion
6 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
6 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.