Forum Discussion

menace911's avatar
menace911
Regular Visitor
4 years ago
Solved

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 usin...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    4 years ago

    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.

v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
4 years ago

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.