cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Create new column based on 3 other columns

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 !!

1 ACCEPTED SOLUTION
Super User

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",
{
{"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",
{
{"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"}),
#"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:

1. Grouped by task and *D, but used GroupKind.Local so it would break the groups by the repeating tasks. So not all 2s were grouped together. Only Groups of 2s were.
2. Then I grouped the entire thing again by the ID and task.
3. Each grouping added an index, and preserved all rows.
4. Then I expanded carefully the indexes and all rows.
5. Then used a formula to find where the groups were 1 for first index and something for the second. If the first, just returned the task. For the 2nd and following, added b, c, d, etc.

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.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
3 REPLIES 3
Super User

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",
{
{"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",
{
{"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"}),
#"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:

1. Grouped by task and *D, but used GroupKind.Local so it would break the groups by the repeating tasks. So not all 2s were grouped together. Only Groups of 2s were.
2. Then I grouped the entire thing again by the ID and task.
3. Each grouping added an index, and preserved all rows.
4. Then I expanded carefully the indexes and all rows.
5. Then used a formula to find where the groups were 1 for first index and something for the second. If the first, just returned the task. For the 2nd and following, added b, c, d, etc.

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.

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Helper I

Amazing thanks!

Super User

Glad I was able to help out @rodfernandez

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors