Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all
Recently I've started to go over some reports I created when I started learning to use power query / powerbi. Whilst I can see the improvements in my capabilities I am by no means 'there' yet, hence why I'm coming to you guys for potential help / ideas!
Long story short I have a task based data set. Within this data I need to create a column to add a reporting group based on the task name so I can then pivot the dates against these report groups for each associated project. The reporting groups are contained within another spreadsheet (to try and make it easier to maintain for my colleagues).
Task list data set:
Project Number | Task Name | Forecasted Date |
1001 | Step 1 (old name) | 01/04/2023 |
1001 | Step 2 | 01/06/2023 |
1001 | Step 2a | 02/06/2023 |
1002 | Step 1 (new name) | 01/05/2023 |
1002 | Step 2 (new name) | 01/07/2023 |
Reporting groups:
Task Name | Reporting Group |
Step 1 (old name) | Step 1 |
Step 1 (new name) | Step 1 |
Step 2 (old name) | Step 2 |
Step 2 (new name) | Step 2 |
Step 2a | Step 3 |
Desired results:
Project Number | Task Name | Forecasted Date | Reporting Group |
1001 | Step 1 (old name) | 01/04/2023 | Step 1 |
1001 | Step 2 (old name) | 01/06/2023 | Step 2 |
1001 | Step 2a | 02/06/2023 | Step 3 |
1002 | Step 1 (new name) | 01/05/2023 | Step 1 |
1002 | Step 2 (new name) | 01/07/2023 | Step 2 |
At the moment this achieved via hard coded with if and else statements when the column is created. However this is highly undesirable for future use, I dont want my colleagues having to enter in the Power Query editor to change something and reduce the system demands etc
This (the Desired Results table) is then pivoted so show the following:
Project Number | Step 1 | Step 2 | Step 3 |
1001 | 01/04/2023 | 01/06/2023 | 02/06/2023 |
1002 | 01/05/2023 | 01/07/2023 |
Any help would be greatly appericated!
Thanks
Iain
You can try the code below.
Note that "Step 2" does not exist in the Reporting Groups table, so it won't match anything. But I suspect that is a typo in your data.
let
//Change next line to reflect actual Tasks data source
Source = Excel.CurrentWorkbook(){[Name="Tasks"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Project Number", Int64.Type}, {"Task Name", type text}, {"Forecasted Date", type date}}),
//Read in the ReportingGroups table
Source2 = Excel.CurrentWorkbook(){[Name="ReportingGroups"]}[Content],
#"Changed Type2" = Table.TransformColumnTypes(Source2,{
{"Task Name", type text}, {"Reporting Group", type nullable text}}),
//Join the tables
// Expand table and remove now unneeded Task Name column
join = Table.NestedJoin(#"Changed Type", "Task Name",#"Changed Type2", "Task Name", "Joined",JoinKind.LeftOuter),
#"Expanded Joined" = Table.ExpandTableColumn(join, "Joined", {"Reporting Group"}, {"Reporting Group"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Joined",{"Task Name"}),
//Group by Project Number, then Pivot each subGroup with no aggregation
#"Grouped Rows" = Table.Group(#"Removed Columns", {"Project Number"}, {
{"pivot", each Table.Pivot(_, List.RemoveNulls(List.Distinct([Reporting Group])), "Reporting Group","Forecasted Date")}
}),
//Expand the pivoted subtables
// then sort the column headers and type them as type date
#"Expanded pivot" = Table.ExpandTableColumn(#"Grouped Rows", "pivot", {"Step 1", "Step 3", "Step 2"}, {"Step 1", "Step 3", "Step 2"}),
#"Sort Columns" = Table.ReorderColumns(#"Expanded pivot", List.Sort(List.RemoveFirstN(Table.ColumnNames(#"Expanded pivot"),1))),
typeDates = Table.TransformColumnTypes(#"Sort Columns", List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Sort Columns"),1), each {_, type date}))
in
typeDates
Tasks
Reporting Groups
Results
Hi ronrsnfld
Thank you for your reply its very insightful and I hope one day I can fully understand it in its entirety! I was wondering if there was a way of doing this without requiring a merger?
The reason I ask is I'm trying to create this as part of a data flow that will automatically refresh. However data flows containing mergers require an increase licence level that I am unable to obtain. Also, sometimes the task names are require a contains which admittedly I could potentially work around by expanding the reporting groups reference list.
Your help is greatly appreciated.
Iain
I'm surprised you cannot use the Table.NestedJoin command. Where can I find a list of the allowable commands for your license level?
So far as doing a contains, that may be possible, but you would need to provide some explicit rules to avoid confusions. For example, in your data example, the substring Step 2 is contained in Step 2 (old name); Step 2 (new name) and also Step 2a
Hello
Here is the warning. When this appears automatic refresh fails / is disabled.
I'm trying to use the following but without success:
Table.AddColumn(#"Choose columns", "Reporting Group", each if Text.Contains([Task Name], #"Schedule Reporting Groups" [Task Name]) then #"Schedule Reporting Groups" [Reporting Group] else null)
Keep getting an error (and the above warning in return. But when I replace it with:
Table.AddColumn(#"Choose columns", "Reporting Group", each if Text.Contains([Task Name], "Step 1 (old name)") then "Step 1" else null)
which it works fine.
What the ultimate aim is, is to create data flow that I can pull on for multiple reports and allow my colleagues to fulfill the Admin role by allowing them to update and amend the reporting groups names (as time advances, thing change) without going into the coding side of things. I've lost enough hours due to poorly placed commas in my time!
Tbh I'm really surpirsed that Microsoft didn't replicate vlookup function / add functionality to utilise reference tables. I sure I must be missing something here!
Again thank you for your help on this!
Iain
Vlookup functionality can be obtained using the various Table.Join functions. So far as the issue with data flows and computed entitites, perhaps this article on Power BI Dataflows - Reuse without a premium subscription may be of value. You may need someone with more experience than I to sort this out.
I have no experience with dataflows, but perhaps the problem is not the joining of the tables, but rather the refreshing of the primary table.
Thats a fair point to be honest. When I think Microsoft and BI reporting together, my mind always jumps to Excel and its capabilities!
I just wanted to say thank you for your help by the way. Whilst you havent been able to give me excatly what I was after, you have taught me something new and I am very grateful for that and i hope that makes your time worth while!
Thank you very much for your help and guidance
Iain