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 team ,
I am really new to this and require assitance in pulling off this query .
I managed to get this query binging , But i want to add a column TimesheetId to the results which is a part of Timesheet dataset .
Please advise
= let
Source = Table.NestedJoin(ResourceTimephasedDataSet,{"ResourceId", "StartOfWeekWcapacity"},Timesheets,{"TimesheetOwnerId", "StartDate"},"NewColumn"),
#"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"StatusDescription"}, {"NewColumn.StatusDescription"}),
#"Replaced Value" = Table.ReplaceValue(#"Expanded NewColumn",null,"Not Yet Created",Replacer.ReplaceValue,{"NewColumn.StatusDescription"}),
#"Merged Queries" = Table.NestedJoin(#"Replaced Value",{"ResourceId"},Resources,{"ResourceId"},"NewColumn"),
#"Expanded NewColumn2" = Table.ExpandTableColumn(#"Merged Queries", "NewColumn", {"EmploymentType","ResourceName", "ResourceDepartments", "TimesheetManager"}, {"EmploymentType","ResourceName", "ResourceDepartments", "TimesheetManager"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded NewColumn2",{"ResourceId"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"StartOfWeekWcapacity", "WeekStarting"}, {"NewColumn.StatusDescription", "Status"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"ResourceDepartments", "TimesheetManager", "ResourceName", "WeekStarting", "Status"}),
#"Sorted Rows" = Table.Sort(#"Reordered Columns",{{"ResourceDepartments", Order.Ascending}, {"TimesheetManager", Order.Ascending}, {"EmploymentType", Order.Ascending}, {"ResourceName", Order.Ascending}, {"WeekStarting", Order.Ascending}}),
#"Added Custom" = Table.AddColumn(#"Sorted Rows", "SummaryStatus", each if [Status]="Approved" then [Status] else if [Status]="Submitted" then [Status] else "Not Submitted")
in
#"Added Custom"
Hi @Tanveer041989 ,
Could you please check if you can expand the Time Sheet ID Column from "New Column" in Step #"Expanded NewColumn"?
such as modify the first two query:
Source = Table.NestedJoin(ResourceTimephasedDataSet,{"ResourceId", "StartOfWeekWcapacity"},Timesheets,{"TimesheetOwnerId", "StartDate"},"NewColumn"),
#"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"StatusDescription"}, {"NewColumn.StatusDescription"},{"NewColumn.TimesheetId"})
Best regards,
Hi ,
thank you so much for looking into this . I have tried that earlier and i get an error when i do that .
Error :
Expression.Error: 5 arguments were passed to a function which expects between 3 and 4.
Details:
Pattern=
Hi @Tanveer041989 ,
How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?
Best regards,
Hi @Tanveer041989 ,
Sorry for our mistake in shared query, could you please try to the following query?
Source = Table.NestedJoin(ResourceTimephasedDataSet,{"ResourceId", "StartOfWeekWcapacity"},Timesheets,{"TimesheetOwnerId", "StartDate"},"NewColumn"),
#"Expanded NewColumn" = Table.ExpandTableColumn(Source, "NewColumn", {"StatusDescription", "TimesheetID"}, {"NewColumn.StatusDescription", "NewColumn.TimesheetId"})
Please change the TimesheetId to the name of column that you want to get in the Timesheets Table
Best regards,
Hi @Tanveer041989 ,
Without the first table from where you are creating the Nested table is difficult to replicate your code, but looking at what you have did you tried to do a merge between the Timesheet table and this one and expanding the ID column?
If this doesn't answer your question can you please share a mockup of your data?
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |