Can some solve this question in Power BI? write earlier statement twice.

[Anonymous]

basically, I want to do it in power BI, first table find the [eariler] in 3rd column, and find [differece] btw two dates, filter [difference] to 1.

run the filter one more time. Please help!!!!!

right now, I am able to do it for the first table, but not the second one.

[Anonymous · Accepted solution]

Does this code perform what you want?

Note that I am presuming that your excel table is called Table.

let   Table_AddOffsetColumn = (   Source as table,  RecordColumnName as text, optional Columns as list, optional  offset as number   ) =>
let
     offset = if offset = null then -1 else offset,
    // Prefix = if prefix = null then "Prior" else prefix,
   Columns = List.Buffer(if Columns = null then Table.ColumnNames(Source) else Columns), 

     SourceSelect = if Columns = null then Source else Table.SelectColumns(Source,Columns),

     ShiftedList = if offset < 0 then List.Repeat({null}, -offset)  &  List.RemoveLastN(Table.ToRecords(SourceSelect ),-offset)
                else List.RemoveFirstN(Table.ToRecords(SourceSelect ),offset) &  List.Repeat({null}, offset),
    
    Combine1 = Table.ToColumns(Source) & {ShiftedList},
    Combine2     = Table.FromColumns(Combine1, Table.ColumnNames(Source) & {RecordColumnName} )
 in
    Combine2,

    SourceData = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(SourceData,{{"Column1", type datetime}, {"Column2", type text}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Column2", Order.Ascending}, {"Column1", Order.Ascending}}),
    Buffer = Table.Buffer( #"Sorted Rows"),
    GetPriorColumn = Table_AddOffsetColumn(Buffer, "Prior",null,-1 ),
    #"Expanded Prior" = Table.ExpandRecordColumn(GetPriorColumn, "Prior", {"Column1", "Column2"}, { "Earlier", "Column4"}),
    AddColumn3 = Table.AddColumn(#"Expanded Prior", "Column3", each Duration.TotalDays([Column1]-[Earlier])),
    #"Filtered Rows" = Table.SelectRows(AddColumn3, each [Column2] <> null and [Column2]=[Column4]  and ([Column3] = 1))
in
    #"Filtered Rows"

[Anonymous]

Hey mcybulski, thank you so much for the respond. Do you think there is anyway this could be done by Dax function instead of in the Power Query? Thanks!!!

[Anonymous]

Sorry Vincent, I am not a DAX expert. I primarly use PQ within Excel to generate journal entries.