Forum Discussion

iainh's avatar
iainh
Frequent Visitor
4 years ago

Creating a reporting group column in Power Query

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 NameForecasted Date

1001

Step 1 (old name)01/04/2023
1001Step 2 01/06/2023
1001Step 2a 02/06/2023
1002Step 1 (new name)01/05/2023
1002Step 2 (new name)01/07/2023

  

Reporting groups:

Task NameReporting 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 NameForecasted DateReporting Group

1001

Step 1 (old name)01/04/2023Step 1
1001Step 2 (old name)01/06/2023Step 2
1001Step 2a 02/06/2023Step 3
1002Step 1 (new name)01/05/2023Step 1
1002Step 2 (new name)01/07/2023Step 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 1Step 2Step 3 

1001

01/04/202301/06/202302/06/2023
100201/05/202301/07/2023 

 

Any help would be greatly appericated!

 

Thanks 

 

Iain

Need Help
Tips and Tricks

6 Replies

  • ronrsnfld's avatar
    ronrsnfld
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      iainh
      Frequent Visitor
      4 years ago

      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's avatar
        ronrsnfld
        Icon for Super User rankSuper User
        4 years ago

        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