Incremental Refresh DataFlow

[Anonymous]

Hi, I created a dataflow , and followed all steps from this video : https://www.youtube.com/watch?v=aAzSZbrICxE

instead of using a.xlsx as in the video I 'm using a sql server database :

this is my script (Advanced editor):

let

  Source = Sql.Database("172.16.0.5", "DABRA", [Query = "SELECT TOP 10#(lf)YEARWEEK_DATE,#(lf)YEARMONTH_DATE,#(lf)CONVERT(date, CONVERT(varchar(8), (yearmonth_date*100+01)), 112) as Periodo,#(lf)branchid,#(lf)ubicacion,#(lf)-- banner,#(lf)CCODIGO_CI,#(lf)art_name,#(lf)item_id,#(lf)cod_articulo,#(lf)art_matriz,#(lf)-- Calidad,#(lf)-- descripcion,#(lf)-- codigoProveedor,#(lf)-- Talle,#(lf)-- marca,#(lf)-- Proveedor,#(lf)-- clase,#(lf)-- Categoria,#(lf)-- Sub_Categoria,#(lf)-- Coleccion,#(lf)-- Matriz,#(lf)-- Genero,#(lf)-- Familia,#(lf)FLG_Descuento,#(lf)cantidad_stock,#(lf)Precio_Costo_SIN_DESCUENTO,#(lf)PRECIO_COSTO_CON_DESCUENTO,#(lf)PDV_Dexter,#(lf)PDV_StockCenter,#(lf)PDV_Moov,#(lf)PDV_NikeShops,#(lf)PDV_eDexter,#(lf)PDV_eMeli,#(lf)PDV_eMoov,#(lf)PDV_ESTOCK_CENTER,#(lf)Mg_Pct_DX,#(lf)Mg_Pct_SC,#(lf)Mg_Pct_MV,#(lf)Mg_Pct_NSP,#(lf)CANAL ,#(lf)audit_datetime#(lf)FROM bi_tbl_tablero_stock#(lf)where#(lf)YEARMONTH_DATE >=((year(GETDATE())-1)*100+01);"]),

  #"Changed column type" = Table.TransformColumnTypes(Source, {{"branchid", type text}}), 

  New = #"Changed column type", 

  AMinDate = List.Min([Periodo]), 

  FilteredHistory = Table.SelectRows(History, each [Periodo] < AMinDate), 

  CombineOldNew = Table.Combine({FilteredHistory,New})

in

  CombineOldNew

but in the end I ´n getting this error :

"here is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?"

what am I doing wrong?  thanks in advance!

[Anonymous]

Hi @Anonymous ,

Please update your M query codes as below, the part with red font is updated one...

let
Source = Sql.Database(
"172.16.0.5",
"DABRA",
[
Query
= "SELECT TOP 10#(lf)YEARWEEK_DATE,#(lf)YEARMONTH_DATE,#(lf)CONVERT(date, CONVERT(varchar(8),
(yearmonth_date*100+01)), 112) as Periodo,#(lf)branchid,#(lf)ubicacion,#(lf)-- banner,#(lf)CCODIGO_CI,#(lf)art_name,#(lf)item_id,#(lf)cod_articulo,#(lf)art_matriz,#(lf)-- Calidad,#(lf)-- descripcion,#(lf)-- codigoProveedor,#(lf)-- Talle,#(lf)-- marca,#(lf)-- Proveedor,#(lf)-- clase,#(lf)-- Categoria,#(lf)-- Sub_Categoria,#(lf)-- Coleccion,#(lf)-- Matriz,#(lf)-- Genero,#(lf)-- Familia,#(lf)FLG_Descuento,#(lf)cantidad_stock,#(lf)Precio_Costo_SIN_DESCUENTO,#(lf)PRECIO_COSTO_CON_DESCUENTO,#(lf)PDV_Dexter,#(lf)PDV_StockCenter,#(lf)PDV_Moov,#(lf)PDV_NikeShops,#(lf)PDV_eDexter,#(lf)PDV_eMeli,#(lf)PDV_eMoov,#(lf)PDV_ESTOCK_CENTER,#(lf)Mg_Pct_DX,#(lf)Mg_Pct_SC,#(lf)Mg_Pct_MV,#(lf)Mg_Pct_NSP,#(lf)CANAL ,#(lf)audit_datetime#(lf)FROM bi_tbl_tablero_stock#(lf)where#(lf)YEARMONTH_DATE >=((year(GETDATE())-1)*100+01);"
]
),
#"Changed column type" = Table.TransformColumnTypes(Source, {{"branchid", type text}}),
New = #"Changed column type",
AMinDate = List.Min(New[Periodo]),
FilteredHistory = Table.SelectRows(History, each [Periodo] < AMinDate),
CombineOldNew = Table.Combine({FilteredHistory, New})
in
CombineOldNew

Best Regards

[amitchandak]

@Anonymous , Create a calculated column first with this and then come and replace code here

[Anonymous]

Hi, @amitchandak , thanks for the answer. I don´t understand what calculated column do I have to create? and what code do I have to replace?