cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
mcybulski
Solution Specialist
Solution Specialist

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
Solution Specialist
Solution Specialist

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

Anonymous
Not applicable

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
Solution Specialist
Solution Specialist

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors