Log In
Register
Home
December 4, 2021
Offline
Okay, I have a challenge here. I used a formula, and it worked, however I am would prefer to do this completely within Power Query. If I use the formulas, when PQ is refreshed, the formulas have to be re-added. Currently I have a VBA script that does that. But again I would rather not go that way. My formulas result in a Yes/No response, however I am good with a True/False as well. My main objective is to replace each of these column formulas with a custom PQ formula (rather than the original "null" when I created the column. My issue is on the sheet where this data is (second screenshot). There is a record for each training taken, so a person can appear multiple times. So I need Power Query to look at Column A of the Training Report (Column Header: "User Full Name") as the 1st criteria and look at Column I (Column Header "Curriculum Title"). Since the training report has multiple records for every name and training title AND this data is on another sheet, I am having a difficult time determining the best course.
See Answer
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
Hi Sherry,
If you create a null column in PQ and use a formula in that column, at next refresh it will be replaced indeed. But if you don't add this column in PQ and you add it manually in the output table, the formula will be preserved as it will be identified as a manually added column.
Not sure I understand what you mean: you just want PQ to look at column I based on user Full Name? Then what PQ should do?
December 4, 2021
Offline
Westt first I never that PQ would refresh my null columns, which is why I wrote a VBA code to add them back in. What I did NOT know is that if they were added OUTSIDE of PQ like you stated, PQ would NOT refresh those 12 new columns. The PQ Refresh would ONLY refresh the initial 2 columns that were built in PQ. I did not know this, ty.
What I am trying to do. Well figure out the best way to approach this problem. For example on my PA_CAPA" Sheet my formula is as shown below. It looks at the "Training Report" sheet to find a name and title. As I previously stated the names appear multiple times, one record for each training the user takes. So the training names appear multiple times.
=IF(COUNTIFS(Training_Report[User Full Name], [@[Primary Approver]], Training_Report[Curriculum Title], "AMER - LATAM - SSC - CAPA Specialist") > 0, "Yes", "No")
Since I was unaware of doing this OUTSIDE of PQ would prevent my formulas from being erased. I am just trying to determine if that (formula outside of PQ) would be better, or if figuring our a way to access the other table. The table name is: Training_Report and the query is Training Report.
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
To search into another table, create a combined key in that table: UserName * Title
Then load the keys into a variable,
KeysList = List.Buffer(YourTable[CombinedKeyColumn]),
Now you just have to add a new column with a formula:
=if List.PositionOf(KeysList, Training_Report[User Full Name] & Training_Report[Curriculum Title])>0 then "Yes" else "No"
Of course, you can create the combined key in the current table as well.
It is case sensitive, so a good idea is to clean, trim and make the keys lower case to avoid errors.
Answers Post
December 4, 2021
Offline
@Catalin Bombea,
Worked like a charm! I knew what I wanted to do, I just did not know how to get there! Thanks so much. Adding this to my PQ Arsenol!
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
PowerPoint
November 8, 2013
Offline
There is a mistake in my formula:
=if List.PositionOf(KeysList, Training_Report[User Full Name] & Training_Report[Curriculum Title])>-1 then "Yes" else "No"
If a key is NOT found, List.PositionOf returns -1, so the formula should return Yes for anything greater than -1. If List.PositionOf returns 0, it still means that the key is found, it's the very first key in list. (results are in base zero, not in base 1)
1 Guest(s)
