Forum Discussion
Can some solve this question in Power BI? write earlier statement twice.
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.
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"
3 Replies
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"