Forum Discussion

evadung's avatar
evadung
Frequent Visitor
3 years ago
Solved

Index column

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. I...
  • adudani's avatar
    3 years ago

    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"