Compare Multiple XML Files

[ibourega]

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 possible to develop such application in Power BI

thank you for your support.

Imbrg

[ams1 · Accepted solution]

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]

Hello,

I am attaching two xml files that I would liek to compare.

thank you again for your support.

Xml files 

[ams1]

HI @ibourega 

Thanks for the samples.

Doing a quick TEXT diff between the 2 XMLs I noticed the diffs are:

• Maschinenparameter\MixerDuration\Value - 120 VS 10
• Produktparameter\BlueColor\Value - 150 VS 50

Questions:

1. Do you currently have an example of how the diff output would look like (ex. what "report"/visualization are you currently using for the Diff)? 
2. Can one XML have a Recipe with a ParameterGroup (identified by Name) that is MISSING from another XML? Ex. can "Maschinenparameter" be missing from another XML?
3. Similar to 1. - Can one XML have an AnalogParameter (identified by name) (ex. MixerRotationSpeed) that is MISSING from another XML?
4. Can the Name element be used as the KEY - ex if one xml has a recipe named "Purple" and another one doesn't have it, then this is considered a diff?

[ibourega]

Hello ams1

here are the answers to your questions

1. I would liek to ahve a kind of table whee the differences are highlighed. Ultimatly I should be able to compare up to 5 recipes. so 6 colums : the first one for the parameter name (that is changing) and other to show the values

2. Yes I can have multiple parameter groups in a recipe. The parameter grops are a kind of container of parameters

3. We can add parameters but if it's too difficul to implement, forget about it

4. The recipe name is a unique name so all recipes that we are comparing should have a different name. We do not compare this parameter

thank you so much for your help.

imbrg

[ams1]

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]

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 · Accepted solution]

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]

Hello ams1

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

Thanks

imbrg

[ibourega · Accepted solution]

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]

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...
  • https://blog.crossjoin.co.uk/2015/09/15/referencing-individual-cell-values-from-tables-in-power-quer...

[ibourega]

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.

[ams1]

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"

[ams1]

Hi,

Nice problem, I think doable, but at least I need more details -> please provide some samples for the XMLs - if you post them, don't forget to remove confidential information.

There are other important details for this XML Diff/Change Data Capture-ish, but at least I need to first see some samples.