Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
This is a measure"Row_number() Over (partition By product category Order by [OrderDate])" which I used in Tableau, how can I translate it in powerBI? Thanks
Solved! Go to Solution.
Hi @Anonymous ,
Suppose the dataset loaded into Power BI is like below.
Here are two options to add such a row numer, via DAX or Power Query.
Option1: Adding a calculated column via DAX.
row_number = RANKX ( FILTER ( Query1, Query1[Product category] = EARLIER ( Query1[Product category] ) ), [OrderDate], , ASC )
Option2: Adding a nested index in Query Editor mode.
In Query Editor mode, sort the [OrderDate] Ascending.
Please refer to bleow code.
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true), Query1_Sheet = Source{[Item="Query1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Query1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"OrderDate", type date}, {"Product category", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"OrderDate", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Product category"}, {{"index", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded index" = Table.ExpandTableColumn(#"Grouped Rows", "index", {"OrderDate", "Index"}, {"index.OrderDate", "index.Index"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded index",{{"index.OrderDate", "OrderDate"}, {"index.Index", "Index"}}) in #"Renamed Columns"
Best regards,
Yuliana Gu
Hi @Anonymous ,
Suppose the dataset loaded into Power BI is like below.
Here are two options to add such a row numer, via DAX or Power Query.
Option1: Adding a calculated column via DAX.
row_number = RANKX ( FILTER ( Query1, Query1[Product category] = EARLIER ( Query1[Product category] ) ), [OrderDate], , ASC )
Option2: Adding a nested index in Query Editor mode.
In Query Editor mode, sort the [OrderDate] Ascending.
Please refer to bleow code.
let Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true), Query1_Sheet = Source{[Item="Query1",Kind="Sheet"]}[Data], #"Promoted Headers" = Table.PromoteHeaders(Query1_Sheet, [PromoteAllScalars=true]), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"OrderDate", type date}, {"Product category", type text}}), #"Sorted Rows" = Table.Sort(#"Changed Type",{{"OrderDate", Order.Ascending}}), #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Product category"}, {{"index", each Table.AddIndexColumn(_, "Index",1,1), type table}}), #"Expanded index" = Table.ExpandTableColumn(#"Grouped Rows", "index", {"OrderDate", "Index"}, {"index.OrderDate", "index.Index"}), #"Renamed Columns" = Table.RenameColumns(#"Expanded index",{{"index.OrderDate", "OrderDate"}, {"index.Index", "Index"}}) in #"Renamed Columns"
Best regards,
Yuliana Gu
Hi Gu,
Thank you so much for help!
Jenny
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |