Retrieve text value from field in a Parent Task using WBS

[txcvega]

I am hoping to get a bit of direction with my issue. 

I am connecting to a Project Online database to retrieve the Tasks table where I am attempting to get a text value from a Parent Task using WBS to populate to all the Child Tasks. 

Example of my data:

In a project schedule, we have Scheduled tasks (e.g. ABC-01-002263-03-Scheduled Task) that are under a Timesheet summary task. These scheduled tasks are assigned to resources so the task appears on their weekly timesheet. Resources also have the ability to add Ad Hoc tasks (e.g. abc-01-002263-03-Ad Hoc Task) into their timesheets that are designated Child tasks of already scheduled tasks. Every task has an ERP ID field that displays ERP sync data. 

Is Summary	WBS	Task Name	ERP ID
TRUE	11.1.11	Timesheets	Timesheets - ABC-01-002263
TRUE	11.1.11.1	ABC-01-002263-03-Scheduled Task	ABC-01-002263-03
FALSE	11.1.11.1.1	abc-01-002263-03-Ad Hock Task	000000035703-0010-
TRUE	11.1.11.4	ABC-01-002263-06-Scheduled Task	ABC-01-002263-06
FALSE	11.1.11.4.1	abc-01-002263-06-Ad Hock Task 1	000000035703-0070-
FALSE	11.1.11.4.2	abc-01-002263-06-Ad Hock Task 2	000000035703-0070-
FALSE	11.1.11.4.3	abc-01-002263-06-Ad Hock Task 3	000000035703-0070-
FALSE	11.1.11.4.4	abc-01-002263-06-Ad Hock Task 4	000000035703-0070-
TRUE	11.2.11	Timesheets	Timesheets - ABC-01-001908
TRUE	11.2.11.1	ABC-01-001908-04-Scheduled Task	ABC-01-001908-04
FALSE	11.2.11.1.1	abc-01-001908-04-Ad Hock Task	000000030480-0030-
TRUE	11.2.11.2	ABC-01-001908-75-Scheduled Task	ABC-01-001908-75
FALSE	11.2.11.2.1	abc-01-001908-75-Ad Hock Task	000000030480-0150-

My issue is to be able to grab text values from the parent task ERP ID field and populate all nodes in the same WBS with a portion of the ERP ID info into a new field (Parent ERP ID).

Is Summary	WBS	Task Name	ERP ID	Parent ERP ID
TRUE	11.1.11	Timesheets	Timesheets - ABC-01-002263	ABC-01-002263
TRUE	11.1.11.1	ABC-01-002263-03-Scheduled Task	ABC-01-002263-03	ABC-01-002263
FALSE	11.1.11.1.1	abc-01-002263-03-Ad Hock Task	000000035703-0010-	ABC-01-002263
TRUE	11.1.11.4	ABC-01-002263-06-Scheduled Task	ABC-01-002263-06	ABC-01-002263
FALSE	11.1.11.4.1	abc-01-002263-06-Ad Hock Task 1	000000035703-0070-	ABC-01-002263
FALSE	11.1.11.4.2	abc-01-002263-06-Ad Hock Task 2	000000035703-0070-	ABC-01-002263
FALSE	11.1.11.4.3	abc-01-002263-06-Ad Hock Task 3	000000035703-0070-	ABC-01-002263
FALSE	11.1.11.4.4	abc-01-002263-06-Ad Hock Task 4	000000035703-0070-	ABC-01-002263
TRUE	11.2.11	Timesheets	Timesheets - ABC-01-001908	ABC-01-001908
TRUE	11.2.11.1	ABC-01-001908-04-Scheduled Task	ABC-01-001908-04	ABC-01-001908
FALSE	11.2.11.1.1	abc-01-001908-04-Ad Hock Task	000000030480-0030-	ABC-01-001908
TRUE	11.2.11.2	ABC-01-001908-75-Scheduled Task	ABC-01-001908-75	ABC-01-001908
FALSE	11.2.11.2.1	abc-01-001908-75-Ad Hock Task	000000030480-0150-	ABC-01-001908

Is it easier to do this in Power Query or in the report using DAX? Any suggestions?

[mahoneypat · Accepted solution]

Here is one way to get your desired result from your example data.  To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZK9DoIwEIBfpWGm5PrDjyMajYOT4EQYEJpg0Dig7y8gxBQOKu3QNr37+jV3SWLF58vesi3GnGayZhffHqoulXrV2oFQEm53FBgF4NwTVmqPk502XQuiIGiUl6p431VB4qyukIiOdAhPkYbqYNk112FhQY7PvBpQ8B3C9Zs7AAYU05LTRz2jlodqSUzL07QIm4r5vdgUx404vgYnjDixBieNODmP+1WB/91ZbAMBkqx3VhtEQS6UsI8Y/4ojnTXAZjoLZAC0XbE/ddXTH/Vdk5bvoloc0Wpgi1rMbbXSDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Is Summary " = _t, #"WBS " = _t, #"Task Name " = _t, #"ERP ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Is Summary ", type logical}, {"WBS ", type text}, {"Task Name ", type text}, {"ERP ID", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [#"Task Name "] = "Timesheets" then Text.AfterDelimiter([ERP ID], "-") else Text.BeforeDelimiter([#"Task Name "], "-", 2)),
    #"Trimmed Text" = Table.TransformColumns(#"Added Custom",{{"Custom", Text.Trim, type text}})
in
    #"Trimmed Text"

Regards,

Pat