Mismatch Fields Per Employee

There are two rows per Emp ID which are from two different sources. You need to list down Emp IDs and then column names which are not matched against those Emp IDs.. For example, in rows 4 & 5 (Emp ID – 252591), weights are 70 & 71. And cities are Sadler and Sadlar. Hence, Weight, City are the mismatched columns for rows 4 & 5. Don’t consider first 2 columns for mismatch as they are not mismatched.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 20
Challenge Difficulty: ⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Mismatch Fields Per Employee with Power Query

Power Query solution 1 for Mismatch Fields Per Employee, proposed by Pavel A.:

let
  Source = input, 
  subTblColumnNames = List.Buffer(List.Skip(Table.ColumnNames(Source), 2)), 
  subTblColumnCount = List.Count(subTblColumnNames), 
  PQ_magic = Table.Group(
    Source, 
    {"Emp ID"}, 
    {
      {
        "Mismatched Columns", 
        each 
          let
            subTbl = Table.RemoveColumns(_, {"Source", "Emp ID"}), 
            vecDifferences = List.Transform(
              Table.ToColumns(subTbl), 
              (actColumnValues) => if List.IsDistinct(actColumnValues) then 1 else null
            ), 
            vecPositions = List.PositionOf(vecDifferences, 1, subTblColumnCount), 
            vecDiffColumns = List.Transform(vecPositions, each subTblColumnNames{_})
          in
            Text.Combine(vecDiffColumns, ", "), 
        type text
      }
    }
  )
in
  PQ_magic

Power Query solution 2 for Mismatch Fields Per Employee, proposed by Bhavya Gupta:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  SourceCount = List.Count(List.Distinct(Source[Source])), 
  Unpivot = Table.UnpivotOtherColumns(Source, {"Source", "Emp ID"}, "Attribute", "Value"), 
  Group = Table.Group(
    Unpivot, 
    {"Emp ID"}, 
    {
      {
        "All", 
        each Table.Group(
          _, 
          {"Source"}, 
          {
            {
              "All", 
              each Table.CombineColumns(
                Table.TransformColumnTypes(_, {{"Value", type text}}), 
                {"Attribute", "Value"}, 
                Combiner.CombineTextByDelimiter(":", QuoteStyle.None), 
                "Merged"
              )[Merged]
            }
          }
        )[All]
      }
    }
  ), 
  CustomColumn = Table.AddColumn(
    Group, 
    "Custom", 
    each List.Repeat(
      {
        Text.Combine(
          List.Distinct(
            List.Transform(
              List.Difference(List.Union([All]), List.Intersect([All])), 
              each Text.BeforeDelimiter(Text.From(_), ":")
            )
          ), 
          ", "
        )
      }, 
      SourceCount
    )
  ), 
  RemovedCol = Table.RemoveColumns(CustomColumn, {"All"}), 
  ExpectedOutput = Table.ExpandListColumn(RemovedCol, "Custom")
in
  ExpectedOutput

Power Query solution 3 for Mismatch Fields Per Employee, proposed by Eric Laforce:

let
  Source = Excel.CurrentWorkbook(){[Name = "tData20"]}[Content], 
  FieldNames = List.Skip(Table.ColumnNames(Source), 2), 
  Group = Table.Group(
    Source, 
    {"Emp ID"}, 
    {
      {
        "Mismatched Columns", 
        each 
          let
            R    = Table.ToRecords(_), 
            FWDV = List.Select(FieldNames, (f) => Record.Field(R{0}, f) <> Record.Field(R{1}, f)), 
            CDF  = Text.Combine(FWDV, ", ")
          in
            {CDF, CDF}
      }
    }
  ), 
  Expand = Table.ExpandListColumn(Group, "Mismatched Columns")
in
  Expand

Power Query solution 4 for Mismatch Fields Per Employee, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

Par3:
#"Removed Columns1" = Table.RemoveColumns(#"Added Conditional Column5",{"Name", "Weight", "Date", "County", "City", "State", "Custom", "Source.1", "Emp ID.1", "Name.1", "Weight.1", "Date.1", "County.1", "City.1", "State.1", "Custom.1"}),
 #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Removed Columns1", {"Source", "Emp ID"}, "Attribute", "Value"),
 #"Removed Columns2" = Table.RemoveColumns(#"Unpivoted Columns",{"Attribute", "Source"}),
 #"Grouped Rows" = Table.Group(#"Removed Columns2", {"Emp ID"}, {{"MissMatchColumns", each Text.Combine([Value],","), type text}}),
 Custom3 = #"Changed Type",
 Custom4 = Table.NestedJoin(Custom3,{"Emp ID"},#"Grouped Rows",{"Emp ID"},"Custom3",JoinKind.LeftOuter),
 #"Removed Other Columns" = Table.SelectColumns(Custom4,{"Emp ID", "Custom3"}),
 #"Expanded Custom3" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom3", {"MissMatchColumns"}, {"MissMatchColumns"}),
 #"Sorted Rows" = Table.Sort(#"Expanded Custom3",{{"Emp ID", Order.Ascending}})
in
 #"Sorted Rows"

Power Query solution 5 for Mismatch Fields Per Employee, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

Part 2:
 #"Removed Columns" = Table.RemoveColumns(#"Expanded Added Index1",{"Index", "Index.1"}),
 #"Added Conditional Column" = Table.AddColumn(#"Removed Columns", "1", each if [Name.1] = [Name] then null else "Name"),
 #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "2", each if [Weight.1] = [Weight] then null else "Weight"),
 #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "3", each if [Date.1] = [Date] then null else "Date"),
 #"Added Conditional Column3" = Table.AddColumn(#"Added Conditional Column2", "Custom.2", each if [County.1] = [County] then null else "Coutnry"),
 #"Added Conditional Column4" = Table.AddColumn(#"Added Conditional Column3", "Custom.3", each if [City.1] = [City] then null else "City"),
 #"Added Conditional Column5" = Table.AddColumn(#"Added Conditional Column4", "Custom.4", each if [State.1] = [State] then null else "State"),

Power Query solution 6 for Mismatch Fields Per Employee, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 #"Changed Type" = Table.TransformColumnTypes(Source,{{"Source", type text}, {"Emp ID", Int64.Type}, {"Name", type text}, {"Weight", Int64.Type}, {"Date", type date}, {"County", type text}, {"City", type text}, {"State", type text}}),
 Condition = Table.AddColumn(#"Changed Type", "Custom", each if [Source] = "SAP" then "SAP" else "Snowflake"),
 #"Filtered Rows" = Table.SelectRows(Condition, each ([Custom] = "SAP")),
 #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 1, 1, Int64.Type),
 Custom1 = Condition,
 #"Filtered Rows1" = Table.SelectRows(Custom1, each ([Custom] = "Snowflake")),
 #"Added Index1" = Table.AddIndexColumn(#"Filtered Rows1", "Index", 1, 1, Int64.Type),
 Custom2 = Table.NestedJoin(#"Added Index1",{"Index"},#"Added Index",{"Index"},"Added Index1",JoinKind.LeftOuter),
 #"Expanded Added Index1" = Table.ExpandTableColumn(Custom2, "Added Index1", {"Source", "Emp ID", "Name", "Weight", "Date", "County", "City", "State", "Custom", "Index"}, {"Source.1", "Emp ID.1", "Name.1", "Weight.1", "Date.1", "County.1", "City.1", "State.1", "Custom.1", "Index.1"}),

Power Query solution 7 for Mismatch Fields Per Employee, proposed by Matthias Friedmann:

let
 Source = Excel.CurrentWorkbook(){[Name = "EmpInfo"]}[Content], 
 #"Grouped Rows" = Table.Group(
 Source, 
 {"Emp ID"}, 
 {
 "Mismatched Columns", 
 each Text.Combine(
 Table.SelectRows(
 Table.Skip(Table.Transpose(Table.DemoteHeaders(_)), 2), 
 each [Column2] <> [Column3]
 )[Column1], 
 (", ")
 )
 }
 )
in
 #"Grouped Rows"

*The conventional 4 step code you can compare here:
https://www.linkedin.com/posts/matthiasfriedmann_powerbi-powerquery-dax-activity-6989978718190346240-Sppj

Power Query solution 8 for Mismatch Fields Per Employee, proposed by Antriksh Sharma:

let
  Source = DataSource, 
  GroupedRows = Table.Group(
    Source, 
    {"Emp ID"}, 
    {
      {
        "Count", 
        each [
          T = _, 
          DataCols = Table.RemoveColumns(T, {"Source", "Emp ID"}), 
          ColNames = Table.ColumnNames(DataCols), 
          GetCols = List.Transform(
            ColNames, 
            (Col) =>
              if Table.RowCount(Table.Distinct(Table.SelectColumns(DataCols, Col))) > 1 then
                Col
              else
                null
          ), 
          Result = Table.AddColumn(
            T[[Emp ID]], 
            "Mismatched Columns", 
            each Text.Combine(List.RemoveNulls(GetCols), ", "), 
            type text
          )
        ][Result]
      }
    }
  ), 
  RemovedOtherColumns = Table.SelectColumns(GroupedRows, {"Count"}), 
  ExpandedCount = Table.ExpandTableColumn(
    RemovedOtherColumns, 
    "Count", 
    {"Emp ID", "Mismatched Columns"}, 
    {"Emp ID", "Mismatched Columns"}
  ), 
  ChangedType = Table.TransformColumnTypes(
    ExpandedCount, 
    {{"Emp ID", Int64.Type}, {"Mismatched Columns", type text}}
  )
in
  ChangedType

Power Query solution 9 for Mismatch Fields Per Employee, proposed by Victor Wang:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Grouped = Table.Group(
    Source, 
    {"Emp ID"}, 
    {
      {
        "Mismatched Columns", 
        (a) =>
          let
            r = Table.ToRecords(a)
          in
            List.Transform(
              r, 
              (b) =>
                Text.Combine(
                  List.Select(
                    List.Skip(Record.FieldNames(b)), 
                    (c) => Record.Field(r{0}, c) <> Record.Field(r{1}, c)
                  ), 
                  ", "
                )
            )
      }
    }
  ), 
  Expand = Table.ExpandListColumn(Grouped, "Mismatched Columns")
in
  Expand

Power Query solution 10 for Mismatch Fields Per Employee, proposed by Sandeep Marwal:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Custom1 = Table.Split(Source, 2), 
  fn = (a) =>
    let
      #"Demoted Headers" = Table.DemoteHeaders(a), 
      #"Transposed Table" = Table.Transpose(#"Demoted Headers"), 
      #"Added Custom" = Table.AddColumn(#"Transposed Table", "Custom", each [Column2] = [Column3]), 
      #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] = false)), 
      Custom3 = Text.Combine(List.Skip(#"Filtered Rows"[Column1]), ","), 
      Custom4 = Table.AddColumn(a, "Mismatched Columns", each Custom3)[
        [Emp ID], 
        [Mismatched Columns]
      ]
    in
      Custom4, 
  result = List.Transform(Custom1, each fn(_)), 
  Custom2 = Table.Combine(result)
in
  Custom2

Solving the challenge of Mismatch Fields Per Employee with Excel

Excel solution 1 for Mismatch Fields Per Employee, proposed by John V.:

Excel solution 2 for Mismatch Fields Per Employee, proposed by محمد حلمي:

Excel solution 3 for Mismatch Fields Per Employee, proposed by محمد حلمي:

Excel solution 4 for Mismatch Fields Per Employee, proposed by 🇰🇷 Taeyong Shin:

Excel solution 5 for Mismatch Fields Per Employee, proposed by Hussein SATOUR:

=IFERROR(
    
     BYROW(
         B2:B19,
          LAMBDA(
              x,
              
               LET(
                   y,
                    FILTER(
                        C2:H19,
                         B2:B19 = x
                    ),
                    z,
                    INDEX(
                        y,
                        1
                    ) = INDEX(
                        y,
                         2
                    ),
                    ARRAYTOTEXT(
                        FILTER(
                            C1:I1,
                             z-1
                        )
                    )
               )
          )
     ),
    
    ""
)

Excel solution 6 for Mismatch Fields Per Employee, proposed by Bhavya Gupta:

=LET(
    Source,
    Table14[Source],
    EmpID,
    Table14[Emp ID],
    HSTACK(
        EmpID,
        MAP(
            EmpID,
            LAMBDA(
                ID,
                ARRAYTOTEXT(
                    FILTER(
                        DROP(
                            Table14[hashtag#Headers],
                            ,
                            2
                        ),
                        BYCOL(
                            FILTER(
                                DROP(
                                    Table14,
                                    ,
                                    2
     &                           ),
                                EmpID=ID
                            ),
                            LAMBDA(
                                x,
                                ROWS(
                                    UNIQUE(
                                        x
                                    )
                                )
                            )
                        )>1,
                        ""
                    )
                )
            )
        )
    )
)

Excel solution 7 for Mismatch Fields Per Employee, proposed by Md. Zohurul Islam:

=LET(
    
    lokupArray,
    B2:B19,
    
    retrnArray,
    C2:H19,
    
    resultArray,
    C1:H1,
    
    z,
    DROP(
        REDUCE(
            "",
            lokupArray,
            LAMBDA(
                x,
                y,
                LET(
                    a,
                    FILTER(
                        retrnArray,
                        lokupArray=y
                    ),
                    b,
                    TAKE(
                        a,
                        1
                    ),
                    c,
                    TAKE(
                        a,
                        -1
                    ),
                    d,
                    b=c,
                    e,
                    FILTER(
                        resultArray,
                        NOT(
                            d
                        )
                    ),
                    f,
                    IFERROR(
                        ARRAYTOTEXT(
                            e
                        ),
                        ""
                    ),
                    g,
                    VSTACK(
                        x,
                        f
                    ),
                    g
                )
            )
        ),
        1
    ),
    
    zz,
    HSTACK(
        lokupArray,
        z
    ),
    
    hdr,
    HSTACK(
        "Emp ID",
        "Mismatched Columns"
    ),
    
    result,
    VSTACK(
        hdr,
        zz
    ),
    
    result
)

Excel solution 8 for Mismatch Fields Per Employee, proposed by Sarun Chimamphant:

=LET(
    th,
    LAMBDA(
        x,
        LAMBDA(
            x
        )
    ),
    a,
    B2:B19,
    b,
    C2:H19,
    s,
    SEQUENCE(
        ROWS(
            a
        )
    ),
    rs,
    s+IF(
        ISODD(
            s
        ),
        1,
        -1
    ),
    cs,
    SEQUENCE(
        ,
        6
    ),
    rb,
    INDEX(
        b,
        rs,
        cs
    ),
    HSTACK(
        a,
        MAP(
            BYROW(
                b,
                LAMBDA(
                    d,
                    th(
                        d
                    )
                )
            ),
            BYROW(
                rb,
                LAMBDA(
                    d,
                    th(
                        d
                    )
                )
            ),
            LAMBDA(
                e,
                f,
                TEXTJOIN(
                    ", ",
                    ,
                    FILTER(
                        C1:H1,
                        e()<>f(),
                        ""
                    )
                )
            )
        )
    )
)

Solving the challenge of Mismatch Fields Per Employee with Power Query

Solving the challenge of Mismatch Fields Per Employee with Excel

Leave a Reply Cancel reply