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 everyone,
First of all, sorry for my poor english 😅
I'm new to Power Query and I'm a bit stuck trying to create the following rule.
I have the following table, with the first 4 columns and I need to create column number 5 (named "NEED THIS")
Row | ID | Time | Task | NEED THIS | |
1 | AAA | 27-10-2021 13:02:55 | 1 | 1 | |
2 | AAA | 27-10-2021 13:02:57 | 1 | ||
3 | AAA | 27-10-2021 13:03:07 | 1 | ||
4 | AAA | 27-10-2021 13:03:58 | 2 | 2 | |
5 | AAA | 27-10-2021 13:04:05 | 2 | ||
6 | AAA | 27-10-2021 13:04:38 | 3 | 3 | |
7 | AAA | 27-10-2021 13:05:22 | 4 | 4 | |
8 | AAA | 27-10-2021 13:05:31 | 4 | ||
9 | AAA | 27-10-2021 13:06:11 | 2 | 2b | |
10 | AAA | 27-10-2021 13:06:24 | 2 | ||
11 | AAA | 27-10-2021 13:07:47 | 3 | 3b | |
12 | AAA | 27-10-2021 13:08:37 | 4 | 4b | |
13 | AAA | 27-10-2021 13:08:43 | 4 | ||
14 | AAA | 27-10-2021 13:09:57 | 3 | 3c | |
15 | AAA | 27-10-2021 13:10:44 | 3 | ||
16 | AAA | 27-10-2021 13:12:20 | 4 | 4c | |
17 | AAA | 27-10-2021 13:13:49 | 5 | 5 | |
18 | AAA | 27-10-2021 13:13:59 | 5 | ||
19 | AAA | 27-10-2021 13:15:02 | 6 | 6 | |
20 | BBB | 29-10-2021 15:22:18 | 1 | 1 | |
21 | BBB | 29-10-2021 15:22:52 | 1 | ||
22 | BBB | 29-10-2021 17:02:11 | 2 | 2 | |
23 | BBB | 29-10-2021 17:05:29 | 3 | 3 | |
24 | BBB | 29-10-2021 17:05:35 | 3 | ||
25 | BBB | 29-10-2021 17:44:03 | 2 | 2b | |
26 | BBB | 29-10-2021 18:01:11 | 3 | 3b | |
27 | BBB | 30-10-2021 10:28:12 | 4 | 4 | |
28 | BBB | 30-10-2021 10:28:51 | 4 | ||
29 | BBB | 30-10-2021 10:40:48 | 5 | 5 | |
30 | BBB | 30-10-2021 11:01:09 | 6 | 6 | |
31 | BBB | 30-10-2021 11:02:50 | 6 |
The column I need is for the purpose of identifying the first time a task occurs for the same ID. For some reason, sometimes it appears that the same task occurs more than once in a row, for those cases, I am only interested in identifying the first time it occurs. for example, in row 7 and 8 the same task (4) occurs more than once in a row, in cases like this I am only interested in identifying the first time it occurs (row 7).
But I am also interested in being able to identify when a task is repeated, but not in a row. For example, in row 12 and 13, task 4 occurs again for the same ID (AAA), but this set of tasks does not occur immediately after or before tasks 4 that are in row 7 and 8, in this case I I would like to be able to identify the first time this task is repeated (row 12), but adding a letter to it, since it is the second time it happens for the same ID (if it is the second time it happens, I would like to add a letter "b", if it is the third time it happens, I would like to add a letter "c" ...and so on).
If someone can help me or guide me, I will be deeply grateful
Thanks !!
Solved! Go to Solution.
Here you go @rodfernandez
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZNNbsQwCIWvMsp6RuLHxA675BrRbNr736FAnGashkqQBfqEIe+x7xNOz2ldV/tSfSG8CAgfyAqkIlbFyPdzn+gfsnbyESRnpMVIlpyU5tVIJyUji4J08ug55yR7T450smakKHm/Eulky0nGTh6vLxk5K+K50VegCDlLZdgJU5mqlnou1dumQjXleq7V2VSqpoWHxTDVajn0jxG+DzZTC0FL6Wxvm8mFpATntL1tJphF8R8vkYFmilnIifYJMslQzNtWnSPjBHygbdscXS7U7aLx4se1YI4KDUcQXvuLVr+syzMHyhlqjZfB3OGgBGUZNCBJ0GLHxaNpab5lmwIew344keovy3CxoNQUxwOjlqMyXljseYcWiza4gOEWRR8WlkFaxgw1vaCjNsD7Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row = _t, ID = _t, Time = _t, Task = _t, #"NEED THIS" = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Time", type datetime}}, "en-BM"),
#"Grouped Rows" =
Table.Group(
#"Changed Type with Locale",
{"ID", "Task"},
{
{"First Group Index", each Table.AddIndexColumn(_, "First Index", 1, 1, Int64.Type )},
{"All Rows", each _, type table [Row=nullable text, ID=nullable text, Time=nullable datetime, Task=nullable text, NEED THIS=nullable text]}}, GroupKind.Local),
#"Grouped Rows1" =
Table.Group(
#"Grouped Rows",
{"ID", "Task"},
{
{"Second Group Index", each Table.AddIndexColumn(_, "Second Index", 1, 1, Int64.Type)}
}
),
#"Expanded Second Group Index" = Table.ExpandTableColumn(#"Grouped Rows1", "Second Group Index", {"First Group Index", "All Rows", "Second Index"}, {"First Group Index", "All Rows", "Second Index"}),
#"Expanded First Group Index" = Table.ExpandTableColumn(#"Expanded Second Group Index", "First Group Index", {"Time", "NEED THIS", "First Index"}, {"Time", "NEED THIS", "First Index"}),
#"Added Letter" =
Table.AddColumn(
#"Expanded First Group Index",
"Letter",
each
if [First Index] = 1 and [Second Index] = 1 then [Task]
else if [First Index] = 1 then [Task] & Character.FromNumber(96+[Second Index])
else null,
type text
),
#"Removed Other Columns" = Table.SelectColumns(#"Added Letter",{"ID", "Task", "Time", "NEED THIS", "Letter"})
in
#"Removed Other Columns"
It returns this. I used null. You could replace that with "" if you want, which is empty.
What I did was:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHere you go @rodfernandez
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZNNbsQwCIWvMsp6RuLHxA675BrRbNr736FAnGashkqQBfqEIe+x7xNOz2ldV/tSfSG8CAgfyAqkIlbFyPdzn+gfsnbyESRnpMVIlpyU5tVIJyUji4J08ug55yR7T450smakKHm/Eulky0nGTh6vLxk5K+K50VegCDlLZdgJU5mqlnou1dumQjXleq7V2VSqpoWHxTDVajn0jxG+DzZTC0FL6Wxvm8mFpATntL1tJphF8R8vkYFmilnIifYJMslQzNtWnSPjBHygbdscXS7U7aLx4se1YI4KDUcQXvuLVr+syzMHyhlqjZfB3OGgBGUZNCBJ0GLHxaNpab5lmwIew344keovy3CxoNQUxwOjlqMyXljseYcWiza4gOEWRR8WlkFaxgw1vaCjNsD7Bw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Row = _t, ID = _t, Time = _t, Task = _t, #"NEED THIS" = _t]),
#"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Time", type datetime}}, "en-BM"),
#"Grouped Rows" =
Table.Group(
#"Changed Type with Locale",
{"ID", "Task"},
{
{"First Group Index", each Table.AddIndexColumn(_, "First Index", 1, 1, Int64.Type )},
{"All Rows", each _, type table [Row=nullable text, ID=nullable text, Time=nullable datetime, Task=nullable text, NEED THIS=nullable text]}}, GroupKind.Local),
#"Grouped Rows1" =
Table.Group(
#"Grouped Rows",
{"ID", "Task"},
{
{"Second Group Index", each Table.AddIndexColumn(_, "Second Index", 1, 1, Int64.Type)}
}
),
#"Expanded Second Group Index" = Table.ExpandTableColumn(#"Grouped Rows1", "Second Group Index", {"First Group Index", "All Rows", "Second Index"}, {"First Group Index", "All Rows", "Second Index"}),
#"Expanded First Group Index" = Table.ExpandTableColumn(#"Expanded Second Group Index", "First Group Index", {"Time", "NEED THIS", "First Index"}, {"Time", "NEED THIS", "First Index"}),
#"Added Letter" =
Table.AddColumn(
#"Expanded First Group Index",
"Letter",
each
if [First Index] = 1 and [Second Index] = 1 then [Task]
else if [First Index] = 1 then [Task] & Character.FromNumber(96+[Second Index])
else null,
type text
),
#"Removed Other Columns" = Table.SelectColumns(#"Added Letter",{"ID", "Task", "Time", "NEED THIS", "Letter"})
in
#"Removed Other Columns"
It returns this. I used null. You could replace that with "" if you want, which is empty.
What I did was:
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAmazing thanks!
Glad I was able to help out @rodfernandez
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |