Forum Discussion
Solved
Compare Multiple XML Files
Hello All, I need to develop a power BI application that will allow to comapre up to 5 xml files. I need to know what has been added, deleted and modified in three different colors. is it possib...
Hi ibourega
Glad it helped -> please don't forget to mark this reply as ANSWER 😉.
Below you will find the code updated with your latest requests.
Also important: get familiar with the code, triple (quadruple) check that it does what it's supposed to -> maybe there are some corner cases we didn't consider and things might go wild - "Maschinenparameter" sounds important 😊
let // define helper functions changeXml = (xmlStr as text ) => // this function changes the xml so it's easier to extract data // text replacement in xml is quick-and-dirty - an alternative would be the Microsoft.XMLDOM from Web.Page(javascript) or python :-D let // remove the Language attribute so we don't get a nested Table replace0 = Text.Replace(xmlStr," Language=""english""",""), // replace <AnalogParameter> replace1 = Text.Replace(replace0,"<AnalogParameter>","<Parameter><Type>AnalogParameter</Type>"), replace2 = Text.Replace(replace1,"</AnalogParameter>","</Parameter>"), // replace <TextParameter> replace3 = Text.Replace(replace2,"<TextParameter>","<Parameter><Type>TextParameter</Type>"), replace4 = Text.Replace(replace3,"</TextParameter>","</Parameter>"), // replace <BinaryParameter> replace5 = Text.Replace(replace4,"<BinaryParameter>","<Parameter><Type>BinaryParameter</Type>"), replace6 = Text.Replace(replace5,"</BinaryParameter>","</Parameter>") // IMPORTANT: if there are other parameter types, they should be added here same as above in replace6, extractParameters = (inTbl as table) => let // I don't like below line as it makes a lot of assumptions - ex. 1 recipe per xml... :-/ Table = inTbl{0}[Table]{0}[Recipes]{0}[Recipe]{0}[ParameterGroups]{0}[ParameterGroup], #"Renamed Columns" = Table.RenameColumns(Table,{{"Name", "ParameterGroupName"}}), #"Expanded Parameters" = Table.ExpandTableColumn(#"Renamed Columns", "Parameters", {"Parameter"}, {"Parameter"}), #"Expanded Parameter" = Table.ExpandTableColumn(#"Expanded Parameters", "Parameter", {"Type", "Name", "OperateBefore", "OperateWhileOnline", "Formula", "HasOperateLimit", "MaterialNumber", "Warehouse", "OperateLowerLimit", "OperateUpperLimit", "ScaleType", "Value"}, {"Type", "Name", "OperateBefore", "OperateWhileOnline", "Formula", "HasOperateLimit", "MaterialNumber", "Warehouse", "OperateLowerLimit", "OperateUpperLimit", "ScaleType", "Value"}) in #"Expanded Parameter", // ============================================================================================ folderContents = Folder.Files("path\to\xmls\folder"), // <= change folder here #"Removed Other Columns1" = Table.SelectColumns(folderContents,{"Content", "Name"}), #"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1",{{"Name", "fileAlias"}}), #"Added xmlFileContents" = Table.AddColumn(#"Renamed Columns", "xmlFileContents", each Text.FromBinary([Content])), changedXmlFileContents = Table.AddColumn(#"Added xmlFileContents", "changedXmlFileContents", each changeXml([xmlFileContents])), #"Added xmlTables" = Table.AddColumn(changedXmlFileContents, "xmlTables", each Xml.Tables([changedXmlFileContents])), #"Added Custom" = Table.AddColumn(#"Added xmlTables", "extractedParameters", each extractParameters([xmlTables])), #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"fileAlias", "extractedParameters"}), #"Expanded extractedParameters" = Table.ExpandTableColumn(#"Removed Other Columns", "extractedParameters", {"ParameterGroupName", "Type", "Name", "OperateBefore", "OperateWhileOnline", "Formula", "HasOperateLimit", "MaterialNumber", "Warehouse", "OperateLowerLimit", "OperateUpperLimit", "ScaleType", "Value"}, {"ParameterGroupName", "Type", "Name", "OperateBefore", "OperateWhileOnline", "Formula", "HasOperateLimit", "MaterialNumber", "Warehouse", "OperateLowerLimit", "OperateUpperLimit", "ScaleType", "Value"}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Expanded extractedParameters", {"fileAlias", "ParameterGroupName", "Type", "Name"}, "Attribute", "Value.1"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[fileAlias]), "fileAlias", "Value.1"), #"Added WeHaveDifferences" = Table.AddColumn(#"Pivoted Column", "WeHaveDifferences", each List.Count(List.Distinct(Record.FieldValues(Record.SelectFields(_, #"Renamed Columns"[fileAlias])))) > 1), #"Filtered Rows" = Table.SelectRows(#"Added WeHaveDifferences", each ([WeHaveDifferences] = true)) in #"Filtered Rows"Thanks so much ams1.
this great I'll test with the real recipe parameters.
is it possible to hide the column Wehave differences
is it possible to have the path where all recipes are located : kind a cell in Excel where I put this path.
thank you so much.
regards,
imbrg
Hell ams1
I marked it as a solution this is what you mean by accet it sas answer, right?
thank you so much for your response and sorry again if I did thing in the wrong way.
Yes, thanks ibourega for marking this as answered.
Final note: to use the GetValue function from the stack overflow, you just need to replace the hardcoded value with the function invocation:
let
// define helper functions
changeXml = (xmlStr as text) =>
// this function changes the xml so it's easier to extract data
// text replacement in xml is quick-and-dirty - an alternative would be the Microsoft.XMLDOM from Web.Page(javascript) or python :grinning_face_with_smiling_eyes:
let
// remove the Language attribute so we don't get a nested Table
replace0 = Text.Replace(xmlStr, " Language=""english""", ""),
// replace <AnalogParameter>
replace1 = Text.Replace(replace0, "<AnalogParameter>", "<Parameter><Type>AnalogParameter</Type>"),
replace2 = Text.Replace(replace1, "</AnalogParameter>", "</Parameter>"),
// replace <TextParameter>
replace3 = Text.Replace(replace2, "<TextParameter>", "<Parameter><Type>TextParameter</Type>"),
replace4 = Text.Replace(replace3, "</TextParameter>", "</Parameter>"),
// replace <BinaryParameter>
replace5 = Text.Replace(replace4, "<BinaryParameter>", "<Parameter><Type>BinaryParameter</Type>"),
replace6 = Text.Replace(replace5, "</BinaryParameter>", "</Parameter>"),
// replace <DigitalParameter>
replace7 = Text.Replace(replace6, "<DigitalParameter>", "<Parameter><Type>DigitalParameter</Type>"),
replace8 = Text.Replace(replace7, "</DigitalParameter>", "</Parameter>")
// IMPORTANT: if there are other parameter types, they should be added here same as above
in
replace8,
extractParameters = (inTbl as table) =>
let
// I don't like below line as it makes a lot of assumptions - ex. 1 recipe per xml... :confused_face:
Table = inTbl{0}[Table]{0}[Recipes]{0}[Recipe]{0}[ParameterGroups]{0}[ParameterGroup],
#"Renamed Columns" = Table.RenameColumns(Table, {{"Name", "ParameterGroupName"}}),
#"Expanded Parameters" = Table.ExpandTableColumn(
#"Renamed Columns", "Parameters", {"Parameter"}, {"Parameter"}
),
#"Expanded Parameter" = Table.ExpandTableColumn(
#"Expanded Parameters",
"Parameter",
{
"Type",
"Name",
"OperateBefore",
"OperateWhileOnline",
"Formula",
"HasOperateLimit",
"MaterialNumber",
"Warehouse",
"OperateLowerLimit",
"OperateUpperLimit",
"ScaleType",
"Value"
},
{
"Type",
"Name",
"OperateBefore",
"OperateWhileOnline",
"Formula",
"HasOperateLimit",
"MaterialNumber",
"Warehouse",
"OperateLowerLimit",
"OperateUpperLimit",
"ScaleType",
"Value"
}
)
in
#"Expanded Parameter",
// ============================================================================================
folderContents = Folder.Files(GetValue("Path_Folder")), // <=== i replaced the hardcoded value with the function invocation
// <= get Folder name from Excel
#"Removed Other Columns1" = Table.SelectColumns(folderContents, {"Content", "Name"}),
#"Renamed Columns" = Table.RenameColumns(#"Removed Other Columns1", {{"Name", "fileAlias"}}),
#"Added xmlFileContents" = Table.AddColumn(
#"Renamed Columns", "xmlFileContents", each Text.FromBinary([Content])
),
changedXmlFileContents = Table.AddColumn(
#"Added xmlFileContents", "changedXmlFileContents", each changeXml([xmlFileContents])
),
#"Added xmlTables" = Table.AddColumn(
changedXmlFileContents, "xmlTables", each Xml.Tables([changedXmlFileContents])
),
#"Added Custom" = Table.AddColumn(#"Added xmlTables", "extractedParameters", each extractParameters([xmlTables])),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom", {"fileAlias", "extractedParameters"}),
#"Expanded extractedParameters" = Table.ExpandTableColumn(
#"Removed Other Columns",
"extractedParameters",
{
"ParameterGroupName",
"Type",
"Name",
"OperateBefore",
"OperateWhileOnline",
"Formula",
"HasOperateLimit",
"MaterialNumber",
"Warehouse",
"OperateLowerLimit",
"OperateUpperLimit",
"ScaleType",
"Value"
},
{
"ParameterGroupName",
"Type",
"Name",
"OperateBefore",
"OperateWhileOnline",
"Formula",
"HasOperateLimit",
"MaterialNumber",
"Warehouse",
"OperateLowerLimit",
"OperateUpperLimit",
"ScaleType",
"Value"
}
),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(
#"Expanded extractedParameters", {"fileAlias", "ParameterGroupName", "Type", "Name"}, "Attribute", "Value.1"
),
#"Pivoted Column" = Table.Pivot(
#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[fileAlias]), "fileAlias", "Value.1"
),
#"Added WeHaveDifferences" = Table.AddColumn(
#"Pivoted Column",
"WeHaveDifferences",
each List.Count(List.Distinct(Record.FieldValues(Record.SelectFields(_, #"Renamed Columns"[fileAlias])))) > 1
),
#"Filtered Rows" = Table.SelectRows(#"Added WeHaveDifferences", each ([WeHaveDifferences] = true)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows", {"WeHaveDifferences"})
in
#"Removed Columns"