Forum Discussion
evadung
3 years agoFrequent Visitor
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...
- 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"
adudani
3 years agoMemorable Member
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"
- evadung3 years agoFrequent Visitor
Works, thank you! 😀