Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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"
what does your data look like?
it can be done in single table, regardless of efficiency.
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.
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).
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 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 :).
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"
How do you know it is only "loading 1000 rows" and not just running into the preview limit of the User Interface for PQ?
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.
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.
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.
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.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
63 | |
61 | |
23 | |
18 | |
12 |