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!

excel
Formulas and Functions
Macros and VBA
power bi
  • 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

     

4 Replies

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        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