Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
Solved! Go to 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"
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! 😀