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.
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
Solved! Go to 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
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
you can use this approach: Incremental Refresh with Slow-Changing Source Data - Microsoft Power BI Community
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.