Lookup Formula in Power Query

[BIWorker10AW]

I have a monthly data consolidated in a single table, I need a Lookup formula for bringing previous month values to the current selected month, Is it Possible in Power Query? Also I cannot create another table and do this, reply only if you can do this using single table.

[wdx223_Daniel]

what does your data look like?

it can be done in single table, regardless of efficiency.

[BIWorker10AW]

Thanks Daniel for replying, 
Yes I have the three columns Parse, Employee Code, Segment with that i have to create Segment Name and Transfer Count as per my requirement. Please Verify the Screenshot to understand this more clearly and this is ofcouse is a sample data with the last 2 columns to be created based on these three columns that if Parse and employee code and Segment is same then "Blank" if emp code and segment is different from previous month then the "Previous Month Segment Name", and for count if no change zero, if change then1 and previous month row -1.

If anyone know this, feel free to answer this.

[ronrsnfld]

Will there be only one entry per month?

It is relatively simple to get the previous month segment, But your rule for the count is not clear:

If you have more than two months, which rule takes precedence for the segment change count? Is the segment to be compared with the preceding or with the subsequent month?  (You could have it being set to 1 based on the previous month but -1 based on the subsequent month).

[BIWorker10AW]

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

[ronrsnfld]

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.

[BIWorker10AW]

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 count

1/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 :).

[ronrsnfld · Accepted solution]

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"

[BIWorker10AW]

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.

[ronrsnfld]

How do you know it is only "loading 1000 rows" and not just running into the preview limit of the User Interface for PQ?

[BIWorker10AW]

1. If i copy the entire rows from table although the column profling is set to entire table it gives me only 1000 rows it always happens? 
2. The Loading with column quality option takes a longer time for a 10k row data after this
   
   Thanks for your time! It was very helpful! Cheers!

[ronrsnfld]

There is something other than the amount of data and/or the algorithm that is causing your slow execution.

I just set up a sample table in Excel with 10,000 rows.

I ran the query and it executed in a fraction of a second, returning all 10,000 rows.

I then ran it in Power BI, with the excel table as the data source. The execution time was measured at about three seconds, whether or not I had profiling set to the first 1000 or the entire data set.

Closing and saving it to Power BI showed that there were, indeed, 10,000 rows.

If I understand what you are doing, you are copying the information from the Power Query editor and pasting it into Excel. If that is the case, that is NOT a method to determine how many rows were loaded or processed.

[BIWorker10AW]

1. Yeah the last point was correct, I was copying the data from the Power Query Editor later i loaded it to PBI and i loaded all the rows.
2. I also checked with the sample data of 500 thousand rows, yes that was loaded very quickly.
3. For my data, I consolidating 4 tables using append queries and using only the appended table  but i have created many columns and condition columns it loaded quickly but after this step it takes a lot of time to load in PQ and even in PBI.
4. I dont know whether it is bug or not, please look at the attached screenshot:
5. After i Click show errors:
   
   
   
6. When i click cancel and check in PQ, there were no errors but everytime when i refresh this comes.
   
   

[ronrsnfld]

Sometimes that is due to a data type error. Do an internet search on the phrase "power query 1 of loaded queries contain errors, but I cannot view the error" and you'll see plenty of ideas. In addition to doing that, if you look at the individual queries, you may see a column that has a red top border -- that should be the column with the errors. You may be able to filter for the error and determine what is going on.

[BIWorker10AW]

Yeah thats the problem, the power query has some loading issues for this file, It takes forever to load and also sucks my memory and battery.

[ronrsnfld]

You may only be seeing 1000 rows in the UI. And yes, it will take a much longer time to load if you set the Preview option to a higher number.  Why do you need more than 1000 rows for the profiling?

If you save it back to Power BI, you should see all your rows.