Forum Discussion

Leon_Redclifft's avatar
Leon_Redclifft
Copper Contributor
Feb 06, 2020
Solved

Need help with Power Query

In the picture, i have one row called Issued_Date which is followed in sequence date. How do I make a step that allow the date to be Not followed by sequence? Please help! Thanks!
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 07, 2020

    Leon_Redclifft 

    If you mean something like this

    you may add index column, after that custom column where to check if previous date is equal to current one then return null else date. Plus cosmetic.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangeType = Table.TransformColumnTypes(
            Source,
            {{"A", type text}, {"Date", type date}}
        ),
        AddIndex = Table.AddIndexColumn(ChangeType, "Index", 0, 1),
        AddCustom = Table.AddColumn(
            AddIndex,
            "Custom",
            each
                if ([Index] > 0) and (AddIndex[Date]{[Index]-1} = AddIndex[Date]{[Index]})
                then null
                else [Date]),
        RemoveOtherColumns = Table.SelectColumns(AddCustom,{"A", "Custom"}),
        RenameColumns = Table.RenameColumns(RemoveOtherColumns,{{"Custom", "Date"}})
    in
        RenameColumns

     

Resources