Forum Discussion
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. 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 |
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"
2 Replies
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"Works, thank you! 😀
