Power Bi desktop help with a query

[Tanveer041989]

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"

[v-lid-msft]

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,

[Tanveer041989]

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=

[v-lid-msft]

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,

[v-lid-msft]

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,

[MFelix]

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