Index column

[evadung]

Hello, 

I want to create End date from column Date below.  I didn't find any solution in query but in DAX with help of index column. So I want to add index column in query to look like this table. Its sorted by item, cost component and date of price changes. I don't havy any idea how to do it. 

Item	Cost component	Date	Price	Index
A	1	13.1.2023	17,1	0
A	1	16.1.2023	17,3	1
A	2	3.1.2023	5	0
A	3	6.1.2023	2,3	0
A	3	13.1.2023	2,2	1
A	3	15.1.2023	2,3	2
A	3	20.1.2023	2,6	3
B	1	15.1.2023	5	0
B	2	15.1.2023	3	0
B	3	15.1.2023	1	0
C	1	2.1.2023	7,1	0
C	1	10.1.2023	7,2	1
C	1	15.1.2023	7,1	2
C	2	3.1.2023	2,5	0
C	3	15.1.2023	1	0
C	3	16.1.2023	1,1	1

[adudani · Accepted solution]

hi @evadung ,

Create a blank query and copy paste the following code into the advanced editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZFRDoAgDEPvwreSUTL9Vo9hvP81dDVBBiR++LM+226cZ9jCFNLzQZBnSXPKNlijzSRc04BYXoJgIVARYpI6g1zLZgD+PwbYAREuwAFaHDAEIAQWjg3Y2yXUVdybDShnJ3f534WOxlzMqj5hCyQh8C3YAVocUIDuxIjqIn4qOpmPyICnwXUD", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [item = _t, #"Cost component" = _t, Date = _t, Price = _t, Index = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"item", type text}, {"Cost component", Int64.Type}, {"Date", type date}, {"Price", type number}, {"Index", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Index"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns",{{"Date", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"item", "Cost component"}, {{"Details", each _, type table [item=nullable text, Cost component=nullable number, Date=nullable date, Price=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Details],"Index",0,1)),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"item", "Cost component", "Details"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", {"item", "Cost component", "Date", "Price", "Index"}, {"item", "Cost component", "Date", "Price", "Index"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"item", type text}, {"Cost component", Int64.Type}, {"Date", type date}, {"Price", type number}, {"Index", Int64.Type}}),
    #"Sorted Rows1" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}})
in
    #"Sorted Rows1"

[evadung]

Works, thank you! 😀