Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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"
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"
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!!!
Sorry Vincent, I am not a DAX expert. I primarly use PQ within Excel to generate journal entries.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.