Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello All,
I have a below table
Date | Number |
2/3/2023 12:00:00 AM | 160 |
4/14/2023 12:00:00 AM | 161 |
5/16/2023 12:00:00 AM | 162 |
6/23/2023 12:00:00 AM
| 163 |
7/23/2023 12:00:00 AM | 164 |
9/26/2023 12:00:00 AM | 165 |
10/20/2023 12:00:00 AM | 166 |
11/3/2023 12:00:00 AM | 167 |
I need to create an index starting from 1 if [Date]>today() otherwise negative index or null values.
Expected Result:
Date | Number | Index | Index |
2/3/2023 12:00:00 AM | 160 | null | -4 |
4/14/2023 12:00:00 AM | 161 | null | -3 |
5/16/2023 12:00:00 AM | 162 | null | -2 |
6/23/2023 12:00:00 AM | 163 | null | -1 |
7/23/2023 12:00:00 AM | 164 | null | 0 |
9/26/2023 12:00:00 AM | 165 | 1 | 1 |
10/20/2023 12:00:00 AM | 166 | 2 | 2 |
11/3/2023 12:00:00 AM | 167 | 3 | 3 |
Tried below logic but its creating 53 rows for each date starting 9/26/2023 and adding index from 1 to 53 for each date and repeating that pattern for all dates starting from 9/26/2023.
Index= Table.AddColumn(#"Changed Type", "Custom", each if [Date]>DateTime.LocalNow() then Table.AddIndexColumn(#"Changed Type", "Index", 1, 1,Int64.Type) else null)
Can you please let me know how to achieve the above result. I am able to achieve this using dax but I need this in Query editor not in DAX.
Thank you!
Solved! Go to Solution.
@ashuaswinireddy here is the output
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@ashuaswinireddy here is the output
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@ashuaswinireddy here is one way, start a blank query -> advanced editor and paste the M code below, change it as you see it.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dc87CoAwEEXRrYSpA/PJD+0EW91ASKGQ0kZ0/4axTWC6c3kwOcN6PBUs7O911huKzSDoUEicYZmJ2pllawVHUvbIvu+sHpBj30W94WDfqaehe/UJZbAf1Jma9oP4Bzx6MEEpHw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Date", type datetime}, {"Number", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each if [Date]>DateTime.LocalNow() then "More" else "Less"),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Custom"}, {{"All", each _, type table [Date=nullable datetime, Number=nullable number, Custom=text]}}),
#"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each if [Custom] = "Less" then Table.AddIndexColumn([All],"Index" ,Table.RowCount([All])*-1,1) else Table.AddIndexColumn([All], "Index", 0,1)),
#"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Date", "Number", "Index"}, {"Date", "Number", "Index"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"Custom", "All"})
in
#"Removed Columns"
👉 Learn Power BI and Fabric - subscribe to our YT channel - @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤️
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
97 | |
85 | |
77 | |
66 | |
62 |
User | Count |
---|---|
110 | |
96 | |
95 | |
64 | |
57 |