Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi, I could use some help finding a way to do the manipulation below in Power Query:
I want to create 2 custom columns "Approver_Name1" and "Approver_Name2" that shows
* The Name of a person that was mentioned in column "T1Comment" based on table "Approver_Name_Table" and "No Approver" when there is no hit based on that table
* In case there are 2 names mentioned in the column "T1Comment" the second name should end up in "Approver_Name2"
Purpose: this table shows a history log of changes to the status of Documents, where each Document has a unique DocumentId. And I'd like to create an overview per approver so that that person gets an action list.
DocumentId | TransitionName | T1Comment | SubmittedDate | Approver_Name1 | Approver_Name2 |
1 | Send for Approval | To be checked by Tom | 22/02/2023 11:00 | Tom | No Approver |
1 | Send back for Editing | Please change extension | 22/02/2023 11:01 | No Approver | No Approver |
1 | Approved | Approved | 22/02/2023 11:02 | No Approver | No Approver |
2 | Send for Approval | Capital mistake please check tom | 22/02/2023 11:05 | Tom | |
2 | Send for Approval | Checked by Tom & Bob | 22/02/2023 11:06 | Tom | Bob |
3 | Send for Approval | Tom/bob | 22/02/2023 11:00 | Tom | Bob |
3 | Send for Approval | Checked byJay | 22/02/2023 11:02 | Jay | No Approver |
3 | Send for Approval | 2nd check jay | 22/02/2023 11:03 | Jay | No Approver |
3 | Send for Approval | Send for Approval to Joe | 22/02/2023 11:05 | Jeoffrey | No Approver |
3 | Approved | Approved | 22/02/2023 11:10 | No Approver | No Approver |
3 | Send back for Editing | Change Editing Required | 11/09/2023 09:11 | No Approver | No Approver |
Approver_Name_Table
Input | Reference_Name |
Bob | Bob |
bob | Bob |
Tom | Tom |
Joe | Jeoffrey |
Jay | Jay |
and so on... | ... |
This is only my second Power Query to date, so I'm a complete rookie. Thanks for the help!
Similar to: https://community.powerbi.com/t5/Power-Query/Query-to-create-column-comparing-text-value-to-column-i...
Related to: https://community.powerbi.com/t5/Power-Query/Find-Text-from-List-in-Rows-with-same-ID-as-current-row...
Solved! Go to Solution.
Hi @Bertenvanloover ,
one approach could be like so:
let
Source = Excel.CurrentWorkbook(){[Name = "Main"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{
{"DocumentId", Int64.Type},
{"TransitionName", type text},
{"T1Comment", type text},
{"SubmittedDate", type any},
{"Approver_Name1", type text},
{"Approver_Name2", type text}
}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each Table.FromRows(
{
List.Distinct(
Table.SelectRows(Approver_Names, (a) => Text.Contains(Text.Lower([T1Comment]), Text.Lower(a[Input])))[Reference_Name]
)
}
)
),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Approver1", "Approver2"})
in
#"Expanded Custom"
It has a different order of the values compared to your sample. Not sure if this is critical, else let me know.
Please also check the file enclosed.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Awesome, works like a charm! Thanks a lot for making my week 🙂
Hi @Bertenvanloover ,
one approach could be like so:
let
Source = Excel.CurrentWorkbook(){[Name = "Main"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{
{"DocumentId", Int64.Type},
{"TransitionName", type text},
{"T1Comment", type text},
{"SubmittedDate", type any},
{"Approver_Name1", type text},
{"Approver_Name2", type text}
}
),
#"Added Custom" = Table.AddColumn(
#"Changed Type",
"Custom",
each Table.FromRows(
{
List.Distinct(
Table.SelectRows(Approver_Names, (a) => Text.Contains(Text.Lower([T1Comment]), Text.Lower(a[Input])))[Reference_Name]
)
}
)
),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Approver1", "Approver2"})
in
#"Expanded Custom"
It has a different order of the values compared to your sample. Not sure if this is critical, else let me know.
Please also check the file enclosed.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
39 | |
20 | |
20 | |
13 | |
12 |