Creating a reporting group column in Power Query

[iainh]

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

[ronrsnfld]

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

[iainh]

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 

[ronrsnfld]

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

[iainh]

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

[ronrsnfld]

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.

[iainh]

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