Filtering edit query

[jorgeslzr]

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

STORE_ID	DATE	STATUS
A1	01/01	Not activated
A1	01/01	Activated
B1	01/01	Activated
C2	01/02	Not 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!

[Anonymous · Accepted solution]

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

[themistoklis]

@jorgeslzr 

Based on the description that you sent I suppose you always get the last status based on date field (which must have time as well).

In power query you should do some ranking and then select the latest values.

You can see the following 2 links:

https://blog.crossjoin.co.uk/2015/05/11/nested-calculations-in-power-query/

https://www.excelguru.ca/blog/2018/06/14/ranking-method-choices-in-power-query/