Incremental refresh for Folder with xml-Files

[lboldrino]

I will use Incremental refresh for my monthly xml-Files. my xml-files dont have any date-column.

but the i can use files-Created date, how can i do it?

here is my xml-files :

code	name	description	packingUnit.Element:Text	orderUnit	country	taxCode	packingDescription	weightDependant	pricingMeasure.unit	pricingMeasure.value	unitPricingMeasure.unit	unitPricingMeasure.value	unitPricingBaseMeasure.unit
20146	Kürbiskernbrot	Kürbiskernbrot          Steinbauer	375 g	STK	AT	10	Stück	FALSE	STK	1	STK	1	STK
20146	Kürbiskernbrot	Kürbiskernbrot          Steinbauer	375 g	STK	AT	10	Stück	FALSE	STK	1	STK	1	STK
20146	Kürbiskernbrot	Kürbiskernbrot          Steinbauer	375 g	STK	AT	10	Stück	FALSE	STK	1	STK	1	STK
20146	Kürbiskernbrot	Kürbiskernbrot          Steinbauer	375 g	STK	AT	10	Stück	FALSE	STK	1	STK	1	STK
20146	Kürbiskernbrot	Kürbiskernbrot          Steinbauer	375 g	STK	AT	10	Stück	FALSE	STK	1	STK	1	STK
23362	Laugenbrötchen	Laugenbrötchen	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23362	Laugenbrötchen	Laugenbrötchen	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23362	Laugenbrötchen	Laugenbrötchen	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23362	Laugenbrötchen	Laugenbrötchen	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23362	Laugenbrötchen	Laugenbrötchen	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23366	Laugencroissant	Laugencroissant	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23366	Laugencroissant	Laugencroissant	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23366	Laugencroissant	Laugencroissant	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23366	Laugencroissant	Laugencroissant	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23366	Laugencroissant	Laugencroissant	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23396	Ab.SchokoDonuts 4er	Ab. Schoko Donuts 4er UTZ      aufgetaut	208 g	STK	AT	10	Packung	FALSE	STK	1	GRM	208	KGM
23396	Ab.SchokoDonuts 4er	Ab. Schoko Donuts 4er UTZ      aufgetaut	208 g	STK	AT	10	Packung	FALSE	STK	1	GRM	208	KGM
23396	Ab.SchokoDonuts 4er	Ab. Schoko Donuts 4er UTZ      aufgetaut	208 g	STK	AT	10	Packung	FALSE	STK	1	GRM	208	KGM
23396	Ab.SchokoDonuts 4er	Ab. Schoko Donuts 4er UTZ      aufgetaut	208 g	STK	AT	10	Packung	FALSE	STK	1	GRM	208	KGM
23396	Ab.SchokoDonuts 4er	Ab. Schoko Donuts 4er UTZ      aufgetaut	208 g	STK	AT	10	Packung	FALSE	STK	1	GRM	208	KGM
23397	Ab.Donuts gezuckert	Ab. Donuts gezuckert 4er       aufgetaut	192 g	STK	AT	10	Packung	FALSE	STK	1	GRM	192	KGM
23397	Ab.Donuts gezuckert	Ab. Donuts gezuckert 4er       aufgetaut	192 g	STK	AT	10	Packung	FALSE	STK	1	GRM	192	KGM
23397	Ab.Donuts gezuckert	Ab. Donuts gezuckert 4er       aufgetaut	192 g	STK	AT	10	Packung	FALSE	STK	1	GRM	192	KGM

code	name	description	packingUnit.Element:Text	orderUnit	country	taxCode	packingDescription	weightDependant	pricingMeasure.unit	pricingMeasure.value	unitPricingMeasure.unit	unitPricingMeasure.value	unitPricingBaseMeasure.unit
20146	Kürbiskernbrot	Kürbiskernbrot          Steinbauer	375 g	STK	AT	10	Stück	FALSE	STK	1	STK	1	STK
20146	Kürbiskernbrot	Kürbiskernbrot          Steinbauer	375 g	STK	AT	10	Stück	FALSE	STK	1	STK	1	STK
20146	Kürbiskernbrot	Kürbiskernbrot          Steinbauer	375 g	STK	AT	10	Stück	FALSE	STK	1	STK	1	STK
20146	Kürbiskernbrot	Kürbiskernbrot          Steinbauer	375 g	STK	AT	10	Stück	FALSE	STK	1	STK	1	STK
20146	Kürbiskernbrot	Kürbiskernbrot          Steinbauer	375 g	STK	AT	10	Stück	FALSE	STK	1	STK	1	STK
23362	Laugenbrötchen	Laugenbrötchen	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23362	Laugenbrötchen	Laugenbrötchen	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23362	Laugenbrötchen	Laugenbrötchen	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23362	Laugenbrötchen	Laugenbrötchen	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23362	Laugenbrötchen	Laugenbrötchen	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23366	Laugencroissant	Laugencroissant	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23366	Laugencroissant	Laugencroissant	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23366	Laugencroissant	Laugencroissant	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23366	Laugencroissant	Laugencroissant	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23366	Laugencroissant	Laugencroissant	85 g	STK	DE	10	Stück	FALSE	STK	1	GRM	85	KGM
23396	Ab.SchokoDonuts 4er	Ab. Schoko Donuts 4er UTZ      aufgetaut	208 g	STK	AT	10	Packung	FALSE	STK	1	GRM	208	KGM
23396	Ab.SchokoDonuts 4er	Ab. Schoko Donuts 4er UTZ      aufgetaut	208 g	STK	AT	10	Packung	FALSE	STK	1	GRM	208	KGM
23396	Ab.SchokoDonuts 4er	Ab. Schoko Donuts 4er UTZ      aufgetaut	208 g	STK	AT	10	Packung	FALSE	STK	1	GRM	208	KGM
23396	Ab.SchokoDonuts 4er	Ab. Schoko Donuts 4er UTZ      aufgetaut	208 g	STK	AT	10	Packung	FALSE	STK	1	GRM	208	KGM
23396	Ab.SchokoDonuts 4er	Ab. Schoko Donuts 4er UTZ      aufgetaut	208 g	STK	AT	10	Packung	FALSE	STK	1	GRM	208	KGM
23397	Ab.Donuts gezuckert	Ab. Donuts gezuckert 4er       aufgetaut	192 g	STK	AT	10	Packung	FALSE	STK	1	GRM	192	KGM
23397	Ab.Donuts gezuckert	Ab. Donuts gezuckert 4er       aufgetaut	192 g	STK	AT	10	Packung	FALSE	STK	1	GRM	192	KGM
23397	Ab.Donuts gezuckert	Ab. Donuts gezuckert 4er       aufgetaut	192 g	STK	AT	10	Packung	FALSE	STK	1	GRM	192	KGM

[lboldrino · Accepted solution]

Here is my Solution:

(StartDate as datetime, EndDate as datetime) =>
let
    Source = SharePoint.Files("https://......sharepoint.com/sites/Test/", [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Extension] = ".xml")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "FileDate", each #date(1970, 1, 1) + 
#duration(0, 0, 0, Number.FromText(
    Text.Replace(Text.Replace([Name], "Test_0_product_", ""), ".xml", ""))/1000)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "FolderDateAsString", each Text.Start(Text.End([Folder Path],8), 7) & ".01"),
    #"Inserted Parsed Date" = Table.AddColumn(#"Added Custom1", "FolderDate", each Date.From(DateTimeZone.From([FolderDateAsString]))),
    #"Changed Type" = Table.TransformColumnTypes(#"Inserted Parsed Date",{{"FileDate", type datetime}, {"FolderDate", type datetime}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", each [FolderDate] >= StartDate and [FolderDate] < EndDate),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows1", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Removed Other Columns1" = Table.SelectColumns(#"Invoke Custom Function1",{"Date modified", "FolderDate", "Transform File"}),
    #"Expanded Transform File" = Table.ExpandTableColumn(#"Removed Other Columns1", "Transform File", {"product"}, {"product"}),
    
    #"Expanded product" = Table.ExpandTableColumn(#"Expanded Transform File", "product", {"name", "description", "packingUnit", "categories", "eans", "prices", "gtins"}, {"name", "description", "packingUnit", "categories", "eans", "prices", "gtins"}),
    #"Expanded packingUnit" = Table.ExpandTableColumn(#"Expanded product", "packingUnit", {"Element:Text"}, {"packageUnit"}),
    #"Expanded categories" = Table.ExpandTableColumn(#"Expanded packingUnit", "categories", {"category"}, {"category"}),
    #"Expanded category" = Table.ExpandRecordColumn(

    // Retaining only first rows in the nested tables as 'records'
    Table.TransformColumns(#"Expanded categories", {"category", Table.First}),

    "category", {"Attribute:name"}, {"firstCategory"}
),
    #"Expanded eans" = Table.ExpandRecordColumn(

    // Retaining only first rows in the nested tables as 'records'
    Table.TransformColumns(#"Expanded category", {"eans", Table.First}),

    "eans", {"ean"}, {"eans.ean"}
),
    #"Added Custom2" = Table.AddColumn(#"Expanded eans", "Custom", each try Table.First(Table.TransformColumnTypes([eans.ean],{{"Element:Text", type text}})) otherwise null),
    #"Expanded Custom" = Table.ExpandRecordColumn(#"Added Custom2", "Custom", {"Element:Text"}, {"firstEan"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"firstEan", type text}, {"eans.ean", type text}, {"firstCategory", type text}, {"packageUnit", type text}, {"description", type text}, {"name", type text}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type1", "EAN", each try [eans.ean] otherwise [#"firstEan"]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom3",{"eans.ean", "firstEan"}),
    #"Expanded prices" = Table.ExpandTableColumn(#"Removed Columns1", "prices", {"price"}, {"price"}),
    #"Expanded price" = Table.ExpandTableColumn(#"Expanded prices", "price", {"value"}, {"price"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded price",{"gtins"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Date modified", "ModifiedDate"}}),
    #"Replaced Value" = Table.ReplaceValue(#"Renamed Columns",".",",",Replacer.ReplaceText,{"price"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Replaced Value",{{"price", type number}}),

    Schema = #table( type table [ModifiedDate = datetime, FolderDate = datetime, name = text, description = text, packageUnit = text, firstCategory = text, price = number, EAN = text], {})
in
      try #"Changed Type2" otherwise Schema

[lbendlin]

you can use this approach: Incremental Refresh with Slow-Changing Source Data - Microsoft Power BI Community