Forum Discussion
Lookup Formula in Power Query
If I understand you correctly, paste the code below into the Advanced Editor. It seems to work with your data sample.
Read the code and comments to better understand the algorithm.
let //change next line to reflect actual data source Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parse", type date}}), //add index column to retain original order #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), //group by "Emp" #"Grouped Rows" = Table.Group(#"Added Index", {"Emp"}, { //For each emp {"all", (t)=> let //ensure date is sorted ascending sort = Table.Sort(t,{"Parse",Order.Ascending}), //shift segment down and up to compare this month to last month and next month shift = Table.FromColumns( Table.ToColumns(sort) & {{null} & List.RemoveLastN(t[Code Segment])} & {List.RemoveFirstN(t[Code Segment]) & {null}}, {"Parse","Emp","Code Segment","Index", "Shift Seg Down", "Shift Seg Up"}), #"Add SegName" = Table.AddColumn(shift,"Segment Name", each if [Code Segment] = ([Shift Seg Down]??[Code Segment]) then null else [Shift Seg Down]), #"Add Xfr Cnt" = Table.AddColumn(#"Add SegName","Transfer Count", each if [Code Segment] <> ([Shift Seg Up]??[Code Segment]) then -1 else if [Code Segment] = ([Shift Seg Down]??[Code Segment]) then 0 else 1), #"Remove Shifted" = Table.RemoveColumns(#"Add Xfr Cnt",{"Shift Seg Down","Shift Seg Up"}) in #"Remove Shifted", type table[Parse=date, Emp=text, Code Segment=text, Index=Int64.Type,Segment Name=text, Transfer Count=Int64.Type] }}), //Expand and sort the Grouped Columns #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Emp"}), #"Expanded all" = Table.ExpandTableColumn(#"Removed Columns", "all", {"Parse", "Emp", "Code Segment", "Index", "Segment Name", "Transfer Count"}), #"Sorted Rows" = Table.Sort(#"Expanded all",{{"Index", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}) in #"Removed Columns1"
Hi,
Thanks for replying here's the sample data:
Note: i have a monthly data upto 12 months of rows average 300 for each month.
| Parse | Emp code | Segment |
| 1/1/2023 | A | Sales |
| 1/1/2023 | A | Sales |
| 1/2/2023 | B | Accounts |
| 1/2/2023 | B | Sales |
| 1/3/2023 | C | Marketing |
| 1/3/2023 | C | Marketing |
| 1/4/2023 | D | Accounts |
| 1/4/2023 | D | Marketing |
Here's the Expected output first if there's a segment change for a employee then his previous month segment should be in the newly changed segment month and another column i need to create is the segment count if there's no change 0, if change then new segment month = 1 and previous month = -1,
Please look at the tables and columns (parse, emp code ) more clearly for better understanding.
| Parse | Emp code | Segment | Segment Name | Tranfer Count |
| 1/1/2023 | A | Sales | 0 | |
| 1/1/2023 | A | Sales | 0 | |
| 1/2/2023 | B | Accounts | -1 | |
| 1/2/2023 | B | Sales | Accounts | 1 |
| 1/3/2023 | C | Marketing | 0 | |
| 1/3/2023 | C | Marketing | 0 | |
| 1/4/2023 | D | Sales | -1 | |
| 1/4/2023 | D | Marketing | Sales | 1 |
Your sample data is showing two entries per employee code, both in the same month. There is no "previous month" for any of the employee codes. Please create a representative data sample.
Hey,
Thank you for pointing out, yeah there is a mistake, here's the updated sample data:Parse Emp Code Segment 1/1/2023 A Sales 1/1/2023 B Marketing 1/2/2023 A Marketing 1/2/2023 B Marketing 1/3/2023 A Marketing 1/3/2023 B Sales
Here's the expected output:
ParseEmp CodeSegmentSegment NameTransfer count1/1/2023 A Sales -1 1/1/2023 B Marketing 0 1/2/2023 A Marketing Sales 1 1/2/2023 B Marketing -1 1/3/2023 A Marketing 0 1/3/2023 B Sales Marketing 1
If there's a segment transfer for the employee then his previous month segment should be displayed in the newly transferred month and his tranfer count should be 1 if there' s a new transfer and -1 in the previous month of the same employee code for the month, if there is no transfer then "blank" in segment name and 0 in count.
Please feel free to ask if you still not able to understand the problem :).If I understand you correctly, paste the code below into the Advanced Editor. It seems to work with your data sample.
Read the code and comments to better understand the algorithm.
let //change next line to reflect actual data source Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Parse", type date}}), //add index column to retain original order #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type), //group by "Emp" #"Grouped Rows" = Table.Group(#"Added Index", {"Emp"}, { //For each emp {"all", (t)=> let //ensure date is sorted ascending sort = Table.Sort(t,{"Parse",Order.Ascending}), //shift segment down and up to compare this month to last month and next month shift = Table.FromColumns( Table.ToColumns(sort) & {{null} & List.RemoveLastN(t[Code Segment])} & {List.RemoveFirstN(t[Code Segment]) & {null}}, {"Parse","Emp","Code Segment","Index", "Shift Seg Down", "Shift Seg Up"}), #"Add SegName" = Table.AddColumn(shift,"Segment Name", each if [Code Segment] = ([Shift Seg Down]??[Code Segment]) then null else [Shift Seg Down]), #"Add Xfr Cnt" = Table.AddColumn(#"Add SegName","Transfer Count", each if [Code Segment] <> ([Shift Seg Up]??[Code Segment]) then -1 else if [Code Segment] = ([Shift Seg Down]??[Code Segment]) then 0 else 1), #"Remove Shifted" = Table.RemoveColumns(#"Add Xfr Cnt",{"Shift Seg Down","Shift Seg Up"}) in #"Remove Shifted", type table[Parse=date, Emp=text, Code Segment=text, Index=Int64.Type,Segment Name=text, Transfer Count=Int64.Type] }}), //Expand and sort the Grouped Columns #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Emp"}), #"Expanded all" = Table.ExpandTableColumn(#"Removed Columns", "all", {"Parse", "Emp", "Code Segment", "Index", "Segment Name", "Transfer Count"}), #"Sorted Rows" = Table.Sort(#"Expanded all",{{"Index", Order.Ascending}}), #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Index"}) in #"Removed Columns1"- Yeah it works well, I have tried even on my company's data it worked well but there's a problem it takes lot of time to load the data even maximum it loads only to 1000 rows in Power Query and i have data around more than 10 thousand currently and i future it will increase to 100 thousand.