Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
lboldrino
Resolver I
Resolver I

Incremental refresh for Folder with xml-Files

‎01-21-2021 01:52 AM

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

 

 

Labels:
Message 1 of 3
436 Views
0
1 ACCEPTED SOLUTION
lboldrino
Resolver I
Resolver I

‎01-21-2021 11:34 PM

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

 

View solution in original post

Message 3 of 3
380 Views
0
2 REPLIES 2
lboldrino
Resolver I
Resolver I

‎01-21-2021 11:34 PM

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

 

Message 3 of 3
381 Views
0
lbendlin
Super User
Super User

‎01-21-2021 01:43 PM

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

Message 2 of 3
393 Views
0

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All