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.

Thingsclump's avatar
Thingsclump
Resolver V
4 years ago

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