Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Comparing 1 json object against another json objects

I have a scenario to get discrepancy records across 2 different JSON objects. I will import the 2 objects into PowerBI Desktop and name as 1 VS 2. 

 

Therefore, what I have is: 

Table1NameIndustryCity Table 2NameIndustryCity
record 1AAC record 1ABC
record 2ABC record 2ABC
record 3 ABC record 3 ABC

 

The result I am looking for is a new table showing: 

New TableNameIndustryCity
record 1YesNoYes
record 2YesYesYes
record 3YesYesYes

 

Could anyone point me with some direction how to get the new table generated? Appreciated a million! 

 

 

General Comment
Need Help
Show and Tell
Tips and Tricks
  • DataInsights's avatar
    DataInsights
    4 years ago

    Anonymous,

     

    Try this in Power Query:

     

    let
  Source = Table.NestedJoin(Table1, {"Record ID"}, Table2, {"Record ID"}, "Table2", JoinKind.Inner),
  RenameColumns = Table.RenameColumns(
    Source,
    {{"Name", "Table1.Name"}, {"Industry", "Table1.Industry"}, {"City", "Table1.City"}}
  ),
  ExpandTable2 = Table.ExpandTableColumn(
    RenameColumns,
    "Table2",
    {"Name", "Industry", "City"},
    {"Table2.Name", "Table2.Industry", "Table2.City"}
  ),
  AddName = Table.AddColumn(
    ExpandTable2,
    "Name",
    each if [Table1.Name] = [Table2.Name] then "Yes" else "No"
  ),
  AddIndustry = Table.AddColumn(
    AddName,
    "Industry",
    each if [Table1.Industry] = [Table2.Industry] then "Yes" else "No"
  ),
  AddCity = Table.AddColumn(
    AddIndustry,
    "City",
    each if [Table1.City] = [Table2.City] then "Yes" else "No"
  ),
  RemoveColumns = Table.RemoveColumns(
    AddCity,
    {
      "Table1.Name",
      "Table1.Industry",
      "Table1.City",
      "Table2.Name",
      "Table2.Industry",
      "Table2.City"
    }
  )
in
  RemoveColumns

     

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    Anonymous,

     

    Try this in Power Query:

     

    let
  Source = Table.NestedJoin(Table1, {"Record ID"}, Table2, {"Record ID"}, "Table2", JoinKind.Inner),
  RenameColumns = Table.RenameColumns(
    Source,
    {{"Name", "Table1.Name"}, {"Industry", "Table1.Industry"}, {"City", "Table1.City"}}
  ),
  ExpandTable2 = Table.ExpandTableColumn(
    RenameColumns,
    "Table2",
    {"Name", "Industry", "City"},
    {"Table2.Name", "Table2.Industry", "Table2.City"}
  ),
  AddName = Table.AddColumn(
    ExpandTable2,
    "Name",
    each if [Table1.Name] = [Table2.Name] then "Yes" else "No"
  ),
  AddIndustry = Table.AddColumn(
    AddName,
    "Industry",
    each if [Table1.Industry] = [Table2.Industry] then "Yes" else "No"
  ),
  AddCity = Table.AddColumn(
    AddIndustry,
    "City",
    each if [Table1.City] = [Table2.City] then "Yes" else "No"
  ),
  RemoveColumns = Table.RemoveColumns(
    AddCity,
    {
      "Table1.Name",
      "Table1.Industry",
      "Table1.City",
      "Table2.Name",
      "Table2.Industry",
      "Table2.City"
    }
  )
in
  RemoveColumns