Forum Discussion
Creating a reporting group column in Power Query
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
typeDatesTasks
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
