Read date from related row and add offset

[eacy]

Hi,

I would like to do the following in the Query Editor in (M).

I have two challenges, the first one is to be able to read a date a another table (Issues) if a boolean is true for that row and write it into [INITIAL].

If the bolean is false I instead need to read [INITIAL TIME] from the row where [ITEM ID] = [RELATED ITEM ID] and then Date.AddDay([INITIAL TIME], [OFFSET]).

I have hardcoded [INITIAL TIME] for "Issue-1" and the three items belonging to it.

The second challenge is to use the [SYSTEM CODE] to decide if I should read the date from the Issues[CREATED] column or Issues[UPDATED] column.

I can decide my self how the content of the [SYSTEM CODE] should look.

This is only an example screenshot because my original is a little to wide to show here. But my M code so far looks like this:

let
    Source = Excel.Workbook(File.Contents("C:\Users\escl\Desktop\Jira Milestone Surveillance\Milestone Events.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type any}}),
    #"Appended Issues" = Table.Combine({#"Changed Type", Issues}),
    #"Removed Columns" = Table.RemoveColumns(#"Appended Issues",{"Column1"}),
    #"Merged Selection" = Table.NestedJoin(#"Removed Columns",{"TYPE"},#"Selection Profiles",{"TYPE"},"NewColumn",JoinKind.Inner),
    #"Expanded Selection ID" = Table.ExpandTableColumn(#"Merged Selection", "NewColumn", {"ID"}, {"NewColumn.ID"}),
    #"Renamed Profile/Issue ID" = Table.RenameColumns(#"Expanded Selection ID",{{"NewColumn.ID", "PROFILE ID"}, {"ID", "ISSUE ID"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Profile/Issue ID",{"NAME", "DESCRIPTION", "TYPE", "STATUS", "CREATED", "UPDATED", "RESOLUTIONDATE", "ASSIGNEE", "PROJECT"}),
    #"Merged Profiles" = Table.NestedJoin(#"Removed Columns1",{"PROFILE ID"},#"Milestone Profiles",{"ID"},"NewColumn",JoinKind.Inner),
    #"Expanded Profile Type" = Table.ExpandTableColumn(#"Merged Profiles", "NewColumn", {"TYPE"}, {"NewColumn.TYPE"}),
    #"Renamed Profile Type" = Table.RenameColumns(#"Expanded Profile Type",{{"NewColumn.TYPE", "PROFILE TYPE"}}),
    #"Merged Item" = Table.NestedJoin(#"Renamed Profile Type",{"PROFILE ID"},Profile_Items,{"PROFILE ID"},"NewColumn",JoinKind.Inner),
    #"Expanded Item Id" = Table.ExpandTableColumn(#"Merged Item", "NewColumn", {"ID", "MILESTONE ID", "TRANSIT RELATION ID"}, {"NewColumn.ID", "NewColumn.MILESTONE ID", "NewColumn.TRANSIT RELATION ID"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded Item Id",{{"NewColumn.ID", "ITEM ID"}, {"NewColumn.MILESTONE ID", "ITEM MILESTONE ID"}, {"NewColumn.TRANSIT RELATION ID", "TRANSIT RELATION ID"}}),
    #"Merged Milestones" = Table.NestedJoin(#"Renamed Columns",{"ITEM MILESTONE ID"},Milestones,{"ID"},"NewColumn",JoinKind.Inner),
    #"Expanded Milestones" = Table.ExpandTableColumn(#"Merged Milestones", "NewColumn", {"ID", "IS SYSTEM", "SYSTEMCODE ID"}, {"NewColumn.ID", "NewColumn.IS SYSTEM", "NewColumn.SYSTEMCODE ID"}),
    #"Renamed Milestone/System Code ID" = Table.RenameColumns(#"Expanded Milestones",{{"NewColumn.ID", "MILESTONE ID"}, {"NewColumn.SYSTEMCODE ID", "MILESTONE SYSTEMCODE ID"}, {"NewColumn.IS SYSTEM", "IS SYSTEM"}}),
    #"Merged System Code" = Table.NestedJoin(#"Renamed Milestone/System Code ID",{"MILESTONE SYSTEMCODE ID"},#"System Codes",{"ID"},"NewColumn",JoinKind.LeftOuter),
    #"Expanded ID/System Code" = Table.ExpandTableColumn(#"Merged System Code", "NewColumn", {"ID", "SYSTEMCODE"}, {"NewColumn.ID", "NewColumn.SYSTEMCODE"}),
    #"Renamed System Code" = Table.RenameColumns(#"Expanded ID/System Code",{{"NewColumn.ID", "SYSTEM CODE ID"}, {"NewColumn.SYSTEMCODE", "SYSTEMCODE"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed System Code",{{"ISSUE ID", Order.Ascending}, {"ITEM ID", Order.Ascending}})
in
    #"Sorted Rows"

Hope that somebody can guide me in the right direction.

[MattAllington]

Sounds like you could add a custom column with an if statement. The syntax is as follows (case sensitive)

 if column1=something then someValue else someOtherValue

you should join the initial table first with a left outlet join to bring in the initial values as a colum, then write the if statement.  After you have what you need, you can delete any other columns

does that help?

[eacy]

Hi Matt,

You are right in order to get the initial date for the ones which are [IS SYSTEM] = true, could be archived with a join and an if statement as you suggest.

The difficult part is how to reuse the dates where [IS SYSTEM] rows are referred in the [RELATED ITEM ID] afterward.

As far as I know there is not way to refer to a previous row in the "else" part of your suggested solution.

I believe I need to somehow read the whole table into a function and process it forth and back.

I have tried to use Table.Skip and Table.ReverseRows but my general knowlegde level is simply not high enought to make it work.