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

Vijay_A_Verma

Most Valuable Professional

4 years ago

Try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XY1BDsAgCAT/wtlEF8XStxj//w3FtBZ72cDOBFojXMr5pkBIKyIkcmJbqAfP2Sqn8I/nj1fHBQXF+hWIUINYF/dymjIj7y9+Pr1qMcHrzYOsj9gH", 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]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"OF", Int64.Type}, {"phase", Int64.Type}, {"Dt_fin", type date}, {"Dt_fin_prev", type date}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "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),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "OTD", each try if [Dt_fin]=null and #"Added Custom"[Dt_fin]{[Index]-1}=null and [OF]=#"Added Custom"[OF]{[Index]-1} 
then #"Added Custom"[OTDTemp]{[Index]-1} 
else [OTDTemp] otherwise ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Index", "OTDTemp"})
in
    #"Removed Columns"

Anonymous

Not applicable

4 years ago

Thank you,

in power query it works but when you load the data (4 M Rows) it doesn't work it loads more Rows than the actual number of rows, and it doesn't stop

Vijay_A_Verma

Most Valuable Professional

4 years ago

What is the source of your data?

Anonymous

Not applicable

4 years ago

the source is SQL server data warehouse

BA_Pete

Super User

4 years ago

Hi Anonymous ,

Can you try this slightly amended code please?

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.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),
    bufOtdTemp = Table.Buffer(addOtdTemp),
    addOtd = Table.AddColumn(addOtdTemp, "OTD", each try if [Dt_fin]=null and bufOtdTemp[Dt_fin]{[Index]-1}=null and [OF]=bufOtdTemp[OF]{[Index]-1} 
then bufOtdTemp[OTDTemp]{[Index]-1} 
else [OTDTemp] otherwise ""),
    remCols = Table.RemoveColumns(addOtd,{"Index", "OTDTemp"})
in
    remCols

Pete