cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vincentwu Frequent Visitor
Frequent Visitor

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

Untitled.png

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.

1 ACCEPTED SOLUTION

Accepted Solutions
mcybulski Member
Member

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

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"

View solution in original post

3 REPLIES 3
mcybulski Member
Member

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

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"

View solution in original post

vincentwu Frequent Visitor
Frequent Visitor

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

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!!!

mcybulski Member
Member

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

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)