Group By not aggregating all rows

[Anonymous]

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

[Anonymous]

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

[juannaya]

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

[Jimmy801]

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 · Accepted solution]

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]

Hello

Could you post the code?

[Anonymous]

Jimmy,

Here is the code for the aggrigation table;

MonthlyRollup = GROUPBY('Usage details', 'Usage details'[BillingPeriodEndDate],'Usage details'[SubscriptionName],'Usage details'[ResourceGroup],

"CostMothlyUsage",

Sumx(CURRENTGROUP(),

'Usage details'[Cost]

))

-T

[Jimmy801]

Hello @Anonymous 

in your first post you were showing a group-funciton in M-language (power query). I suggested you a TransformColumns before the Group-function. Therefore I would love to see your m-code with my suggestion applied, not DAX, as in your last code.

BR

Jimmy

[amitchandak]

@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]

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