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.
Dear Power Bi community,
Do you know how I can write in PowerQuery:
To index the rows based on their date ( earliest to latest) and to index them only if they have a special text value ( for example "text")
After the indexing is done, to write in another new column the value "text2" every 5 rows?
Solved! Go to Solution.
Yes, it's achievable. Sort the data on 2 columns (text, date).
Then do a 'Group By' on Text, using the All Rows aggregation.
Then add an index within each group with code similar to this:
Add a custom column
Table.AddIndexColumn([all], "sub", 1, 1, Int64.Type)
[all] is the name of the All Rows column from the previous step.
Remove the columns that you don't want and expand the column headers to return the required data.
Good luck
Yes, it's achievable. Sort the data on 2 columns (text, date).
Then do a 'Group By' on Text, using the All Rows aggregation.
Then add an index within each group with code similar to this:
Add a custom column
Table.AddIndexColumn([all], "sub", 1, 1, Int64.Type)
[all] is the name of the All Rows column from the previous step.
Remove the columns that you don't want and expand the column headers to return the required data.
Good luck
Make 2 copies of the table (using Duplicate from the interface)
1st table : Filter out rows with text in Column2 (from the column header).
2nd table : Filter out rows with no text in Column2. Sort the Date column ascending. Add an Index (from Add Column menu).
Add a column (with your column name inserted) :
if Number.Mod([IndexCol], 5) = 0 then "SKIP LOT" else ""
---
Append the 2 tables (from the interface)
--
That should do it. Let me know how it goes
Hello, Thanks a lot it worked. Do you know if there is this complication: we have different texts, and I want to index based on the value "texts".
For the first Step I will sort the rows based on ascending date. But how can I index based on the text value? Example is below
For instance:
Text Value | Date | Index |
Text A | 02/12/2021 | 2 |
Text A | 01/12/2021 | 1 |
Text B | 01/12/2021 | 1 |
Text C | 01/12/2021 | 1 |
Text B | 02/12/2021 | 2 |
Text C | 02/12/2021 | 2 |
Text A | 03/12/2021 | 3 |
Text A | 04/12/2021 | 4 |
Yes, with sort function, custom conditional index column and math.
Please provide some data.
Have I solved your problem? Please click Accept as Solution so I don't keep coming back to this post, oh yeah, others may find it useful also ;). |
If you found this post helpful, please give Kudos. It gives me a sense of instant gratification and, if you give me Kudos enough times, magical unicorns will appear on your screen. If you find my signature vaguely amusing, please give Kudos. | Proud to be a Super User! |
So everything is in the same table. I want the results to look like this.
The index based on the column 2 if I have "text" and in a chronological order by date. And every 5 indexes, to write in the new Column : Skip Lot
Column 1 | Column 2 | Date | Index | new Colum |
a | ||||
b | text | 05/12/2021 | 2 | |
c | text | 06/12/2021 | 3 | |
d | ||||
e | text | 07.12/2021 | 4 | |
f | ||||
g | text | 08/12/2021 | 5 | SKIP LOT |
h | text | 01/12/2021 | 1 |
@Anonymous providing sample data and desired output would be a good start
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.