List how long price was active

[MrMP]

Hi All,

I have table structure like following:

Date	Customer	Price
1-1-2021	AA	10
1-2-2021	AA	10
1-3-2021	AA	11
1-4-2021	AA	10
1-5-2021	AA	10
1-6-2021	AA	10

and I have to create the following table:

Customer	Price	Duration in days
AA	10	2
AA	11	1
AA	10	3

Thank you for your help!

[Fowmy · Accepted solution]

@MrMP 

You can do it in Power Query with a single Group By step. After you group by selecting Customer and Price, add this ", GroupKind.Local" at the end of the group by step.

You can paste the code given below on a Blank Query and check.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ11DUyMDJU0lFydAQShgZKsTogYSPswsaowoZQYRPsqk2xC5thCMcCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Customer = _t, Price = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Customer", type text}, {"Price", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer", "Price"}, {{"Count", each Table.RowCount(_), Int64.Type}}, GroupKind.Local)
in
    #"Grouped Rows"

[MrMP]

Thank you.

Is there a chance for this to be dynamic or in dax, since number of dimensions can vary. Sometimes its only a customer, sometimes its customer and product.