Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Group By not aggregating all rows

Hi,

I'm working in Power BI and using the Azure Cost Management connector data.

I need a table with a summary of Monthly Azure usage cost by Subcription-Resource Goup. So I did a Group By but the resulting table is not aggregating all the rows.

 

Here is what the Power Query does;

1) Chanage BillingPeriodEndDate type from Date/Time to Date

2) Group By BillingPeriodEndDate, SubscriptionName, ResourceGroup and create Cost-MonthlyUsage as sum of cost.

 

 

 

 

 

 

 

 

 

But the resulting table is still including multipe rows for each BillingPeriodEndDate, SubscriptionName, ResourceGroup combination, with different Cost-MonthlyUsage (see below)

 

 

 

 

 

Any suggestions on what I'm doing wrong, or how to debug this?

 

Thanks,

T

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Thanks Jimmy,

    I gave it a try but unfortuneatly its still providing the same result.

     

    So I gave up on Power Query and instead figured out that I could acomplish the same thing in DAX with the following;

     
    MonthlySummary = GROUPBY('Usage details', 'Usage details'[BillingPeriodEndDate],'Usage details'[SubscriptionName],'Usage details'[ResourceGroup],
    "CostMothlyUsage",
    Sumx(CURRENTGROUP(),
    'Usage details'[Cost]
    ))
     
    This is getting me what I need and aggrigating properly.
    Thanks for your help!
    -T

9 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , Is there any chance BillingPeriodEndDate has timestamp other than 12 AM ?

     

    In case your date column has timestamp. Just change of datatype will not do it.

     

    Create a date column to remove timestamp portion

    DateTime.Date([BillingPeriodEndDate])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks Amit,

      I'm already changing the Date/Time to Date as step 1 of the transformation. Unfortunealty the problem persists.

      But thaks for the suggestion.

       

      -T

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    Hello Anonymous 

     

    check out this code. Use Tabe.Transformcolumns to transform to date and change type.

    This should help you

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WstA3NtQ3MlAwNFJw9FXSUTJUitVBiJoqBIAEjVAEzSEqjbEJmijFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DateTime = _t, Column2 = _t]),
    TransformToDate = Table.TransformColumns
    (
        Source,
        {
            {
                "DateTime",
                each Date.From(DateTime.From(_,"en-US")),
                type date
            }
        }
    ),
    #"Grouped Rows" = Table.Group(TransformToDate, {"DateTime"}, {{"Sum", each List.Count([Column2]), type number}})

in
    #"Grouped Rows"

     

    Copy paste this code to the advanced editor in a new blank query to see how the solution works.

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks Jimmy,

      I gave it a try but unfortuneatly its still providing the same result.

       

      So I gave up on Power Query and instead figured out that I could acomplish the same thing in DAX with the following;

       
      MonthlySummary = GROUPBY('Usage details', 'Usage details'[BillingPeriodEndDate],'Usage details'[SubscriptionName],'Usage details'[ResourceGroup],
      "CostMothlyUsage",
      Sumx(CURRENTGROUP(),
      'Usage details'[Cost]
      ))
       
      This is getting me what I need and aggrigating properly.
      Thanks for your help!
      -T
      • Jimmy801's avatar
        Jimmy801
        Community Champion
        5 years ago

        Hello

         

        Could you post the code?

         

         

         

         

         

  • juannaya's avatar
    juannaya
    Regular Visitor
    5 years ago

    Hi,

     

    Try making the transfomation Date >Date only, before grouping. It's not enough just changing formats. 

     

    Now that you have just dates, with no time data, you'll be able to make the suggested aggroupation.

     

    Regards,

    Juan

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Could you give it a try to see which lines are seen as different, while appearing to have the same content?

    it could be useful to other people who run into the same problem


    Maybe there is some character not visible ...

     

    Start trying

     

    Table.Distinct(Source, {"Column1", "Column2","Column3"})

    then try

    Table.Distinct(Source, {"Column1"})

     

    then

     

    Table.Distinct(Source, {"Column3"})

     

    and so on  depending on the previuos results