Forum Discussion

jorgeslzr's avatar
jorgeslzr
Frequent Visitor
5 years ago
Solved

Filtering edit query

Hi, I have a data source that has a column with Store_ID, Date, and Status:

STORE_IDDATESTATUS
A101/01Not activated
A101/01Activated
B101/01Activated
C201/02Not Activated

 

As you can see, there can be two status for the same STORE_ID in the same DATE... Since for my measures I do DISTINCTCOUNT on stores, it is useless for me to load row1 (A1 - Not Activated) into my .pbix because I also have it Activated that day. BUT if it is only Not Activated that day (last row for example), I want to keep it in my table.

 

Is there a way I can filter this table in POWER QUERY EDITOR so my .pbix data is much smaller and make it quicker? I want to do this because my data is huge and I could filter that.

 

Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi jorgeslzr ,

    You can edit the applied codes in Advanced Editor to achieve it:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyMjAy0DfUBzH98ksUEpNLMssSS1JTlGJ1MFU4osg64ZV1NkLIGmGaHgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [STORE_ID = _t, DATE = _t, STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"STORE_ID", type text}, {"DATE", type date}, {"STATUS", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DATE", "STORE_ID"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"Details", each _, type table [STORE_ID=nullable text, DATE=nullable date, STATUS=nullable text]}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"STATUS"}, {"Details.STATUS"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Details", each ([Details.STATUS] = "Not activated") and ([Count] = 1))
in
    #"Filtered Rows"

    You can also refer the following blog to achieve it:

    Grouping in Power Query; Getting The Last Item in Each Group

    Best Regards

    Rena

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi jorgeslzr ,

    You can edit the applied codes in Advanced Editor to achieve it:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjRU0lEyMjAy0DfUBzH98ksUEpNLMssSS1JTlGJ1MFU4osg64ZV1NkLIGmGaHgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [STORE_ID = _t, DATE = _t, STATUS = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"STORE_ID", type text}, {"DATE", type date}, {"STATUS", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"DATE", "STORE_ID"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}, {"Details", each _, type table [STORE_ID=nullable text, DATE=nullable date, STATUS=nullable text]}}),
    #"Expanded Details" = Table.ExpandTableColumn(#"Grouped Rows", "Details", {"STATUS"}, {"Details.STATUS"}),
    #"Filtered Rows" = Table.SelectRows(#"Expanded Details", each ([Details.STATUS] = "Not activated") and ([Count] = 1))
in
    #"Filtered Rows"

    You can also refer the following blog to achieve it:

    Grouping in Power Query; Getting The Last Item in Each Group

    Best Regards

    Rena