Power Query - Search an element of column A in entire column B

0 votes

I want to know how many new employees were hired each month. I have a database of employee lists for every month. For this, I gave each employee a code, one with the name and date of the current month and the other with the name and date of the prior month. Hence, I created a formula to search for the code from the month before in the column from the current month; if the formula cannot find the value, it provides an error (#N/A), which denotes that the employee is new. Therefore, it has a conditional that if it finds any value it is equal to 0 and if it does not find anything (#N/A) it is 1.

Example

The problem is that the database is huge and I have it in Power Query but I don't know how to use this formula there.

Mar 30 in Others by Kithuzzz
• 38,010 points
57 views

1 answer to this question.

0 votes

The delta column in PowerQuery is calculated for you if you have two columns, Date and Name. None of the other columns are necessary for you.

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Name", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type","Delta", (x)=>  
    if x[Date]=Date.From(List.Min(#"Changed Type"[Date])) then 0   // results for first dates on list
    else  1-Table.RowCount(Table.SelectRows(#"Changed Type", each Date.AddDays(Date.StartOfMonth(x[Date]),-1)=[Date] and [Name]=x[Name])))
in  #"Added Custom"

enter image description here

answered Mar 30 by narikkadan
• 63,040 points

Related Questions In Others

0 votes
1 answer

How to trick an Excel function that wants a column as input to accept a list of values as if these were in a column

Use VSTACK: vstack to make an array: Use it as value ...READ MORE

answered Mar 18 in Others by narikkadan
• 63,040 points
73 views
0 votes
1 answer

VBA Loop to select then copy a range of cells based on value in column B

Try this: Sub Macro2() Dim ...READ MORE

answered Mar 23 in Others by narikkadan
• 63,040 points
279 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
3,289 views
0 votes
1 answer

Power Query : http POST request with form data

Try using Uri.BuildQueryString and Json.Document. let ...READ MORE

answered Dec 12, 2018 in Power BI by Upasana
• 8,620 points
7,800 views
0 votes
1 answer

Power Query : Adding Columns and Multiple files

The following call, FirstRowAsHeader = Table.PromoteHeaders(TableWithoutHeader) shall replace the ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
1,200 views
0 votes
1 answer

Power Query : making HTTP POST request with form data

Try Using Uri.BuildQueryString and Json.Document let ...READ MORE

answered Feb 14, 2019 in Power BI by Upasana
• 8,620 points
5,127 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,040 points
879 views
0 votes
1 answer

Shortcut to Apply a Formula to an Entire Column in Excel

Try double-clicking on the bottom right hand ...READ MORE

answered Oct 29, 2022 in Others by narikkadan
• 63,040 points
147 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP