Removing latest Date from Cloud data

[menace911]

Hi All,

I have linked Power BI to AWS CURs, I am pulling all data in and finding that depending on my refresh times (as I am baed in Australia) I am getting a portion of a days expense. I am using these latest days for an algorythm on Forecast. 

I am coming into problems because of our 'Savingplan' data, these are future dated and are to remain.

In Power Query I can exclude the latest / max date (this picks the latest date including the SavingPlan and would be 'March 17' as per dataset below:

= Table.SelectRows(RemoveBlanks, let latest = List.Max(SplitProductOrderName[Date]) in each [Date] <> latest)

I can remove a certain date while ignoring the 'ProductUsage.SavingPlan'

= Table.SelectRows(RemoveBlanks, each (([#"lineItem/UsageStartDate"] <> #date(2022, 3, 6) or [#"ProductUsage"] = "SavingPlan")))

I have thought to complete something like:

= Table.AddColumn(RemoveBlanks, "FutureDates", each if [ProductUsage] = "SavingPlan" then 1 else if List.Max([#"lineItem/UsageStartDate"]) = [#"lineItem/UsageStartDate"] then 0 else 1)

so that I can then filter out '0' values.    --- This doesn't work

I have built a small dataset to show 

Please note.

• M Language (I don't want to bring the data into Power BI)
• Today date / Yesterday Date does not reflect the date I am trying to exclude
• CUR tables are millions of lines appended together, I dont see the creation of a couple of columns or duplicating this table to breakdown to the max date feasible. I am trying to complete this with filters only

Thanks

[menace911]

Thanks for your time in this.

[v-jingzhang · Accepted solution]

Hi @menace911 

You can try this M code. The "latest" variable should get the latest date for non-SavingPlan rows. 

= Table.SelectRows(#"Changed Type", let latest = List.Max(Table.SelectRows(#"Changed Type", each [ProductUsage] <> "SavingPlan")[#"lineItem/UsageStartDate"]) in each [ProductUsage] = "SavingPlan" or [#"lineItem/UsageStartDate"] <> latest)

Or you can break down above step into two steps in Advanced Editor like below. In this way, the latestDate will be calculated only once, which may improve the performance than the previous method. Download the attachment to see details. 

    latestDate = List.Max(Table.SelectRows(#"Changed Type", each [ProductUsage] <> "SavingPlan")[#"lineItem/UsageStartDate"]),
    Custom1 = Table.SelectRows(#"Changed Type", each [ProductUsage] = "SavingPlan" or [#"lineItem/UsageStartDate"] <> latestDate)

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[Thingsclump]

Hey @menace911 

I belive the dataset or table that you have shown is input data. Can you please show expected result for same? This will help to give a solution. Please paste input data as table format or html table so that others need not create it again.

Thanks