Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have this problem and if someone can please help me solve this would be great.
Sheet 1 contains a table for Managers and Sheet 2 contains a table for Allocation. I need POWER BI to assign the Header BU based on the following criteria
A. If the manager remains same even if the State changes the HEADER BU will be the first Business Unit from column A (Sheet 1) found against that particular manager or else the HEADER BU will be the first Business Unit from column A (Sheet 1) found against that particular manager
B. If the State remains same but the count of Mangers of a particular STATE is more than 1 then the HEADER BU will be picked up from the Sheet 2 corresponding to that State.
Thank you in advance
Solved! Go to Solution.
Solution in the Power Query part of Power BI, accessible via the Query Editor.
You can paste the code below in the Advanced Editor.
From viewpoint of performance, it is best to have all required data in the same row before adding the custom column:
With input in tables Managers and Allocation, you need:
let Source = Managers, // Steps to merge the table with itself to get the previous manager on the same row as the manager #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter), #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Business Unit", "Manager"}, {"Previous.Business Unit", "Previous.Manager"}), // Group by State to determine in there is more than 1. #"Grouped Rows" = Table.Group(#"Expanded Previous", {"State"}, {{"Count", each Table.RowCount(_), type number}, {"AllData", each _, type table}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Business Unit", "Manager", "Index", "Index.1", "Previous.Business Unit", "Previous.Manager"}, {"Business Unit", "Manager", "Index", "Index.1", "Previous.Business Unit", "Previous.Manager"}), // Merge with Allocation to get its Business Unit in case the count > 1. #"Merged Queries1" = Table.NestedJoin(#"Expanded AllData",{"State"},Allocation,{"State"},"Allocation",JoinKind.LeftOuter), #"Expanded Allocation" = Table.ExpandTableColumn(#"Merged Queries1", "Allocation", {"Business Unit"}, {"Allocation.Business Unit"}), // Now add the custom column #"Added Custom" = Table.AddColumn(#"Expanded Allocation", "Header BU", each if [Manager] = [Previous.Manager] then [Previous.Business Unit] else if [Count] > 1 then [Allocation.Business Unit] else [Business Unit]), // Finishing touches: #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Index.1", "Previous.Business Unit", "Previous.Manager", "Allocation.Business Unit"}) in #"Removed Columns"
Another suggestion, that returns the results from your examples, would be: if a state has more than 1 distinct managers, it gets the business unit from sheet2, otherwise it gets the first business unit for this manager.
let Source = Managers, #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Grouped Rows" = Table.Group(#"Added Index", {"Manager"}, {{"First BU", each List.Min([Business Unit]), type number}, {"AllData", each _, type table}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Business Unit", "State", "Index"}, {"Business Unit", "State", "Index"}), #"Grouped Rows1" = Table.Group(#"Expanded AllData", {"State"}, {{"AllData", each _, type table}, {"Distinct Mgrs per State", each List.Count(List.Distinct([Manager])), type text}}),
#"Expanded AllData1" = Table.ExpandTableColumn(#"Grouped Rows1", "AllData", {"Manager", "First BU", "Business Unit", "Index"}, {"Manager", "First BU", "Business Unit", "Index"}), #"Merged Queries" = Table.NestedJoin(#"Expanded AllData1",{"State"},Allocation,{"State"},"Allocation",JoinKind.LeftOuter), #"Expanded Allocation" = Table.ExpandTableColumn(#"Merged Queries", "Allocation", {"Business Unit"}, {"Allocation.Business Unit"}), #"Added Custom" = Table.AddColumn(#"Expanded Allocation", "Header BU", each if [Distinct Mgrs per State] = 1 then [First BU] else [Allocation.Business Unit] ), #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}), #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Business Unit", "Manager", "State", "Header BU"}) in #"Removed Other Columns"
You didn't test with my last code.
These are the results I get:
From the code in this post:
Can't spend more time on this. The problem is with column Distinct Mgrs per S-V in query MLT_V_FM_ST (2)
It is defined as text, while the content are numbers. This is no error for Power Query, but it is an error when data is loaded to Excel.
(as it is no error for Power Query, you don't get any error records in your error query).
So change the type to (whole) number and the issue is solved.
Solution in the Power Query part of Power BI, accessible via the Query Editor.
You can paste the code below in the Advanced Editor.
From viewpoint of performance, it is best to have all required data in the same row before adding the custom column:
With input in tables Managers and Allocation, you need:
let Source = Managers, // Steps to merge the table with itself to get the previous manager on the same row as the manager #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Added Index1" = Table.AddIndexColumn(#"Added Index", "Index.1", 1, 1), #"Merged Queries" = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index1",{"Index.1"},"Previous",JoinKind.LeftOuter), #"Expanded Previous" = Table.ExpandTableColumn(#"Merged Queries", "Previous", {"Business Unit", "Manager"}, {"Previous.Business Unit", "Previous.Manager"}), // Group by State to determine in there is more than 1. #"Grouped Rows" = Table.Group(#"Expanded Previous", {"State"}, {{"Count", each Table.RowCount(_), type number}, {"AllData", each _, type table}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Business Unit", "Manager", "Index", "Index.1", "Previous.Business Unit", "Previous.Manager"}, {"Business Unit", "Manager", "Index", "Index.1", "Previous.Business Unit", "Previous.Manager"}), // Merge with Allocation to get its Business Unit in case the count > 1. #"Merged Queries1" = Table.NestedJoin(#"Expanded AllData",{"State"},Allocation,{"State"},"Allocation",JoinKind.LeftOuter), #"Expanded Allocation" = Table.ExpandTableColumn(#"Merged Queries1", "Allocation", {"Business Unit"}, {"Allocation.Business Unit"}), // Now add the custom column #"Added Custom" = Table.AddColumn(#"Expanded Allocation", "Header BU", each if [Manager] = [Previous.Manager] then [Previous.Business Unit] else if [Count] > 1 then [Allocation.Business Unit] else [Business Unit]), // Finishing touches: #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"Index", "Index.1", "Previous.Business Unit", "Previous.Manager", "Allocation.Business Unit"}) in #"Removed Columns"
Hi Marcel,
To test the code I added a row as folllowing and I wanted the Header BU to be returned for that row as 105. But this code returns it as 1004. Can you please take look.
Can you clarify the rules?
Especially "stays the same": is this from 1 row to another or do you mean now that:
Please confirm if this is correct or else: what are the corect rules?
Additionally, can you clarify why the rows with manager BP and state ON shoul get 1012, and - in the new example - te rows with manager ALC and state QC should get 105?
Another suggestion, that returns the results from your examples, would be: if a state has more than 1 distinct managers, it gets the business unit from sheet2, otherwise it gets the first business unit for this manager.
let Source = Managers, #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1), #"Grouped Rows" = Table.Group(#"Added Index", {"Manager"}, {{"First BU", each List.Min([Business Unit]), type number}, {"AllData", each _, type table}}), #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Business Unit", "State", "Index"}, {"Business Unit", "State", "Index"}), #"Grouped Rows1" = Table.Group(#"Expanded AllData", {"State"}, {{"AllData", each _, type table}, {"Distinct Mgrs per State", each List.Count(List.Distinct([Manager])), type text}}),
#"Expanded AllData1" = Table.ExpandTableColumn(#"Grouped Rows1", "AllData", {"Manager", "First BU", "Business Unit", "Index"}, {"Manager", "First BU", "Business Unit", "Index"}), #"Merged Queries" = Table.NestedJoin(#"Expanded AllData1",{"State"},Allocation,{"State"},"Allocation",JoinKind.LeftOuter), #"Expanded Allocation" = Table.ExpandTableColumn(#"Merged Queries", "Allocation", {"Business Unit"}, {"Allocation.Business Unit"}), #"Added Custom" = Table.AddColumn(#"Expanded Allocation", "Header BU", each if [Distinct Mgrs per State] = 1 then [First BU] else [Allocation.Business Unit] ), #"Sorted Rows" = Table.Sort(#"Added Custom",{{"Index", Order.Ascending}}), #"Removed Other Columns" = Table.SelectColumns(#"Sorted Rows",{"Business Unit", "Manager", "State", "Header BU"}) in #"Removed Other Columns"
Hi Marcel,
Sorry for the late reply.
To clarify your doubts
To elaborate
Take a look at my previous post: if I apply that simple rule with the associated code, I get the same result.
Thanks for the reply.
Hoiwever, to test this code I added another row (row 11 and 12) and it did not return the result in Header BU column I was looking for row 12. The Header BU for that row should have been 100 and not 121 since Manager remains same and the State changes. Therefore, it should assign the First Business Unit value against that Manager.
Sorry to bug you.
You didn't test with my last code.
These are the results I get:
From the code in this post:
Thanks Marcel a lot. Yes it was my Typo which did not return the desired result otherwise the code works with all the conditions.
I want to add another level to it and I will post in few days.
Hi Marcel,
Hope you are well. I have added another level (Vendor) to the previous spreadsheet.
The logic is if the Distinct Manager per State-Vendor = 1 then #Min BU per Manager-Vendor else Allocation Business Unit. I followed your code and it is working fine with my requirement. It can calculate the Header BU as I desired. However, when the data loads it is generating error and I have no clue why it happens.
If I don't load the data and create only connection and use a pivot to get the connection, I don't se any error. It only occurs if
If you can please take a look and advise how to get rid of it, would be great.
https://drive.google.com/file/d/0B7imabOIHE8QTTFBLWw4aFU5dzg/view?usp=sharing
Thank you in advance.
This is the error I am getting
Can't spend more time on this. The problem is with column Distinct Mgrs per S-V in query MLT_V_FM_ST (2)
It is defined as text, while the content are numbers. This is no error for Power Query, but it is an error when data is loaded to Excel.
(as it is no error for Power Query, you don't get any error records in your error query).
So change the type to (whole) number and the issue is solved.
THanks for your time. It is resolved now.
Hi Marcel,
Thank you very much for providing the code.
It works !!!!
If I find a more complex condition to add I will post here.
Thank you...:)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |