Forum Discussion
Removing latest Date from Cloud data
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
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.
3 Replies
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
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.Thanks for your time in this.
