Forum Discussion

Frequent Visitor

3 years ago

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...

Need Help

ams1

3 years ago

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"

ibourega
3 years ago
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

ams1

Responsive Resident

3 years ago

Hi ibourega ,

Below PowerQuery will just show data from multiple xml files side-by-side so that the user can SEE the differences.

I only extracted the Parameters.

Hope this is what you want and that you can handle adding more files (as many as you want).

let
    xmlFilePaths = Table.FromRecords(
        {
            // 1st file
            [
                filePath = "somepath\Recipe1.xml",
                fileAlias = "file1" // this is the name of the column
            ],
            // 2nd file
            [
                filePath = "somepath\Recipe2.xml",
                fileAlias = "file2" // this is the name of the column
            ]
            // 3rd file...
            // add here as many files as you want!           
        }
    ),
    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",
    #"Added xmlFileContents" = Table.AddColumn(xmlFilePaths, "xmlFileContents", each Text.FromBinary(File.Contents([filePath]))),
    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")
in
    #"Pivoted Column"

Please don't forget to mark this as answer if it helped.

ibourega

Frequent Visitor

3 years ago

Hello ams1

thank you so much for your file, i's just excellent.

I do not have your level of expertise and hence have some questions:

Is it possible to rename the header of the columns of the table by the name of the files like : recipe 1, recipe 2, ... (see picture below)
Is it possible to have the app read let say the 3 or 5 files directly from the directory instead of specifying the name of the recipes to compare?
Is it possible to show only the differences instead of showing all parameters?

Thank you again for your support, very much appreciated.

Imbrg

ams1

Responsive Resident

3 years ago

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.

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"

ibourega
Frequent Visitor
3 years ago
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
- ams1
  Responsive Resident
  3 years ago
  Hi ibourega
  
  First of all please mark my previous reply as ANSWER for the initial issue. 🤗
  
  Imo we should avoid having many "subquestions" in one question/thread - other people that will have the initial XML problem in the future will have difficulty "to read" the solution if there are many additional questions/solutions in the thread.
  
  For further questions/issues please open a separate thread after closing this one.
  
  Please let below be the last answers in this thread (again, feel free to open new threads/questions!):
  "is it possible to hide the column Wehave differences" - yes, right click on the column in PowerQuery and choose Remove
  "is it possible to have the path where all recipes are located : kind a cell in Excel where I put this path" - yes, you can "reference cell value in power query" (google) see:
  https://stackoverflow.com/questions/26989279/how-can-i-reference-a-cells-value-in-powerquery
  https://exceltown.com/en/tutorials/power-bi/power-query-m-language/value-from-excel-cell-directly-to-power-query/
  https://blog.crossjoin.co.uk/2015/09/15/referencing-individual-cell-values-from-tables-in-power-query
ibourega
Frequent Visitor
3 years ago
Hello ams1

another point I would liek to confirm is that each xml fine is one recipe only.

Thanks
imbrg

Forum Discussion

Compare Multiple XML Files

Related content

Merge data from multiple excel files from multiple sharepoint folders

Power Query Combine Multiple Files From Folder

Microsoft Fabric Prevent multiple pipeline triggers when multiple files arrive simultaneously.

Comparing the data between multiple years

Microsoft Fabric – Event trigger fires multiple times when multiple files arrive