Forum Discussion

PaMa's avatar
PaMa
New Member
3 years ago

output in seconds.

Hello everyone, I'm not sure if I'm right here. I've been working with Power BI for a good six months. Since last week I'm stuck on the following scenario in Power Query: A table consists of sever...
Need Help
PaMa's avatar
PaMa
New Member
3 years ago

It's all a bit cumbersome. So, I sorted by PNR. Now I would like to have the difference between the time and the associated PNR. Can this be created in Power Query via a DAX function?

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
3 years ago

Use this where Source needs to be replaced

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    ListPNR = List.Buffer(Source[PNR]),
    ListTime = List.Buffer(Source[Time]),
    CountTbl = Table.RowCount(Source),
    GenResultList = List.Generate(()=>[x=0,i=0], each [i]<CountTbl, each [i=[i]+1, x=if ListPNR{i+1}=ListPNR{i} then List.Max({Duration.From(0), ListTime{i}-ListTime{i+1}}) else 0 ], each try Duration.From([x]) otherwise Duration.From(0)),
    Result = Table.FromColumns(Table.ToColumns(Source) & {GenResultList}, Table.ColumnNames(Source) & {"Difference"})
in
    Result