Log In
Register
Home
May 2, 2021
Offline
Hi All,
I want to seek assistance on how to create nested if conditions to a search text function. I watched one of Mynda's youtube video in the past. (Can't seem to find it anymore. Should've favorited the video).
In the sample file the RAW data, in Column D, there are rows with blank personnel. Personnel is defined based on colors and Region. Cell D4, D5, D6 will be tagged automatically as TBA as the description (Column D) states the word "Core", "Hotel" and "Trim".
If these words appear and if the color is Green (Column B). It will automatically be tagged as TBA (To be Announce).
The only separator is that Core will be tagged for Admin 1, Hotel for Admin 2 and Trim of Admin 3.
Please see the end result sheet.
This is a sample data and I will be working on a big data so it is more likely that I will have multiple description that must be detected.
The Text lookup one is to one I can do however not sure how to do if conditions.
Hopefully you can assist me.
Trusted Members
October 18, 2018
Offline
Try this Mcode
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
CheckColors = Table.AddColumn(Source, "Custom", each if[Colors]="Green" and [Personnel] =null then "TBA" else null),
CheckAdmins = Table.AddColumn(CheckColors, "Custom.1", each if [Colors]="Green" then if Text.Contains([Description],"Core") then "Admin1"
else if Text.Contains([Description],"Hotel") then "Admin2"
else if Text.Contains([Description],"Trim") then "Admin3" else null else null),
MergePersonnel = Table.AddColumn(CheckAdmins, "Personnel1", each Text.Combine({[Personnel], [Custom]}, ""), type text),
MergeAdmins = Table.AddColumn(MergePersonnel, "Admin1", each Text.Combine({Text.From([Admin], "en-US"), [Custom.1]}, ""), type text),
ChangeDateFormat = Table.TransformColumnTypes(MergeAdmins,{{"Assignment", type date}}),
RemoveHelperColumns = Table.SelectColumns(ChangeDateFormat,{"Assignment", "Colors", "Region", "Description", "Personnel1", "Admin1"}),
MoveColumns = Table.ReorderColumns(RemoveHelperColumns,{"Assignment", "Colors", "Region", "Personnel1", "Admin1", "Description"}),
RenameColumns = Table.RenameColumns(MoveColumns,{{"Personnel1", "Personnel"}, {"Admin1", "Admin"}})
in
RenameColumns
File Attached for review.
May 2, 2021
Offline
Hi Alan,
Hope all is well. Thank you for your kind attention about my problem. This works perfectly fine. I would like to confirm if these conditions can be hard coded? What I would like to achieve is to create a connection list (see text function query rev1). What will happen is that whatever is on the description table of the List it will return the column B and A.
Reason behind it is that as Description will vary from time to time.
1 Guest(s)
