Forum Discussion

Loganidk's avatar
Loganidk
Occasional Reader
Dec 12, 2025

Issues With PowerQuery Using 2 Tables with different Data Sets

Im having issues using power query when I try to use 2 tables to make a pivot table. I am using a sheet that pulls information from a system I use for maintenance. The first screenshot is a subset that breaks down sub items for a main "work order". The next 2 screenshots are from a separate table that show the main "work order" and has an "Equipment Pool" that shows who owns the vehicle in question. When trying to create a pivot table, a lot of information shows as blank. (see bottom screenshot) I am wondering what I am doing wrong for it not to be able to pull the "Equipment Pool" when it aligns to the "Asset ID". Any help is appreciated. (For security reasons I have not put all information from the tables, only the relevant ones)

 

 

 

excel

1 Reply

  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Dec 13, 2025

    Hi

    making a stripped down version of your workbook with anonymized information shouldn't take more than 15 mins and without even your query code it's virtually impossible to help

    Only thing that comes in my mind given the info. you posted is a mismatch between [Asset Id]s (or [Work Order Id]s - not clear which field(s) you use to merge the tables) in your Tables. They're Text values meaning they could contain trailing space(s) and/or invisible/unprintable char(s) like Unicode Char 160 (often 'included' with copy/paste from the Web)

    Query1
    Table1 has a couple of those chars (have taken [Asset Id] as ex.):

    let
    Table1 = #table( {"Asset Id"},
        {
            {"123"},
            {"456 "},                               // Has trailing space
            {"789" & Character.FromNumber(160)}     // Has trailing unichar 160
        }
    ),
    Table2 = #table( {"Asset Id", "Equipment"},
        {
            {"123", "ABC"},
            {"456", "DEF"},
            {"789", "GHI"}
        }
    ),
    MergedTables = Table.NestedJoin(
        Table1, "Asset Id",
        Table2, "Asset Id",
        "Table2", JoinKind.LeftOuter
    ),
    ExpandedEquipment = Table.ExpandTableColumn( MergedTables, "Table2", {"Equipment"} )
in
    ExpandedEquipment

    Query1 result:

     

    Query2
    Fixes [Asset Id] on Table1:

    let
    Table1 = #table( {"Asset Id"},
        {
            {"123"},
            {"456 "},                               // Has trailing space
            {"789" & Character.FromNumber(160)}     // Has trailing unichar 160
        }
    ),
    CleanedTable1_AssetId = Table.TransformColumns( Table1,
        {"Asset Id", each
            Text.Replace(
                Text.Trim( _ ),                     // Removes trailing space(s)
                Character.FromNumber(160), ""       // Replaces unichar 160 withe 'nothing'
            )
        }
    ),
    Table2 = #table( {"Asset Id", "Equipment"},
        {
            {"123", "ABC"},
            {"456", "DEF"},
            {"789", "GHI"}
        }
    ),
    MergedTables = Table.NestedJoin(
        CleanedTable1_AssetId, "Asset Id",
        Table2, "Asset Id",
        "Table2", JoinKind.LeftOuter
    ),
    ExpandedEquipment = Table.ExpandTableColumn( MergedTables, "Table2", {"Equipment"} )
in
    ExpandedEquipment

    Query2 result:

    If that doesn't help at all post at least your complete query code please

Resources