Forum Discussion

Anonymous

Not applicable

4 years ago

Solved

OTD_column

OF phase Dt_fin Dt_fin_prev OTD 178239 10 15/10/2020 178239 12 15/10/2022 1 178239 13 16/10/2022 1 151414 14 18/1...

Need Help

BA_Pete

4 years ago

Hi Anonymous ,

Try this slightly updated code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY3LDcAgDEN3yRmJ2HxKZ0Hdf42SAhLpxZLznpzeBVdjuiUI1KJEaKTSijzh5LTTofDH0+LV84KMbPcvWgQMYhXO4s0y32iaM654s46gRsU2uTeH+bw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OF = _t, phase = _t, Dt_fin = _t, Dt_fin_prev = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Dt_fin", type date}, {"Dt_fin_prev", type date}}),
    addIndex = Table.AddIndexColumn(chgTypes, "Index", 0, 1, Int64.Type),
    addOtdTemp = Table.Buffer(Table.AddColumn(addIndex, "OTDTemp", each if [Dt_fin_prev]=null then ""
    else if [Dt_fin_prev]>=Date.From(DateTime.LocalNow()) then 1
    else if [Dt_fin]<=[Dt_fin_prev] then 1 
    else 0)),
    addOtd = Table.AddColumn(addOtdTemp, "OTD", each try if [Dt_fin]=null and addOtdTemp[Dt_fin]{[Index]-1}=null and [OF]=addOtdTemp[OF]{[Index]-1} 
then addOtdTemp[OTDTemp]{[Index]-1} 
else [OTDTemp] otherwise ""),
    remCols = Table.RemoveColumns(addOtd,{"Index", "OTDTemp"})
in
    remCols

The last code should have sped up your query a lot, but may not have removed the multi-load requirement. I'm hoping this version will speed it up a bit more and also *should* remove the multi-load requirement. Even if it doesn't, it should only load twice maximum now.

Pete

BA_Pete

Super User

4 years ago

Hi Anonymous ,

Try this slightly updated code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY3LDcAgDEN3yRmJ2HxKZ0Hdf42SAhLpxZLznpzeBVdjuiUI1KJEaKTSijzh5LTTofDH0+LV84KMbPcvWgQMYhXO4s0y32iaM654s46gRsU2uTeH+bw=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [OF = _t, phase = _t, Dt_fin = _t, Dt_fin_prev = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Dt_fin", type date}, {"Dt_fin_prev", type date}}),
    addIndex = Table.AddIndexColumn(chgTypes, "Index", 0, 1, Int64.Type),
    addOtdTemp = Table.Buffer(Table.AddColumn(addIndex, "OTDTemp", each if [Dt_fin_prev]=null then ""
    else if [Dt_fin_prev]>=Date.From(DateTime.LocalNow()) then 1
    else if [Dt_fin]<=[Dt_fin_prev] then 1 
    else 0)),
    addOtd = Table.AddColumn(addOtdTemp, "OTD", each try if [Dt_fin]=null and addOtdTemp[Dt_fin]{[Index]-1}=null and [OF]=addOtdTemp[OF]{[Index]-1} 
then addOtdTemp[OTDTemp]{[Index]-1} 
else [OTDTemp] otherwise ""),
    remCols = Table.RemoveColumns(addOtd,{"Index", "OTDTemp"})
in
    remCols

Pete

Anonymous

Not applicable

4 years ago

thanks for your reply,

but there is still the same problem

BA_Pete
Super User
4 years ago
Hi Anonymous ,

Does your actual query have any merges or appends in it, or any other step that references another query or makes the query reference itself at another step?

Any of these will cause Power Query to reload the table multiple times when applying to the model.

Pete
Anonymous
Not applicable
4 years ago
Thank you, but it's none of the above
BA_Pete
Super User
4 years ago
Anonymous ,

This is sounding less like a PQ issue.

Any chance you can copy your entire M code query from Advanced Editor and paste into a code window ( </> button above) please? I'll be able to see what might be causing this issue, or if it's even a PQ issue at all.

Make sure to remove/X-out your server/db connection string in the Source step.

Pete