Forum Discussion
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)
4 Replies
Hi
A single line of code doesn't help I'm afraid. You were asked to provide the complete query code
Even with the latter - without some actual data - no guarantee at all there's a chance to identify the root cause of the issue
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 ExpandedEquipmentQuery1 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.Trim( // Removes trailing space(s) Text.Replace( _, Character.FromNumber(160), "" ) // Replaces unichar 160 with '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 ExpandedEquipmentQuery2 result:
If that doesn't help at all post at least your complete query code please
Hello,
Thank you for the help. I am extremely new at trying to use power query but below is the code for the expanded content portion:
= Table.ExpandTableColumn(Source, "Content", {"Work Order Id", "Sub Work Order Id", "Approval Dt", "Asset LIN/TAMCN", "Asset Id", "Item Desc", "Maint Activity", "Priority Cd", "Sub Work Order State Cd", "Work Order Status Cd", "Maint Team Id", "Service Performed", "Service End Dt", "DOD Serial Nbr", "ECC", "Serial Nbr", "Stock Nbr", "Doc Nbr", "Closed Dt", "Work Order Has Asset", "ACC", "Actual Labor Hours", "Asset Certifications", "Asset Location", "Asset UIC", "Avail Dt", "CAGE Code", "CIIC", "Cond Cd", "Custodian Id", "Dmil Cd", "Dollar Amount", "Equipment Pool", "Est Labor Cost", "Est Labor Hours", "Est Non-Labor Cost", "Est Service End Dt", "Estbd By", "Estbd Dt/Time", "Haz Mat Cd", "Incoming Meter Reading", "Incoming Util Report Date", "Job Order Nbr", "Labor Cost", "Last Updtd By", "Maint Loc", "Manufactured Year", "MC Status", "Meter", "Mfr Model Nbr", "Mfr Name", "Mfr Part Nbr", "Mission Critical", "NMC/PMC End Date", "NMC/PMC Start Date", "NMC Total Time in Days", "NMC Total Time in Hours", "NMCB Days", "NMCB Hours", "NMCM Days", "NMCM Hours", "NMCS Days", "NMCS Hours", "Non Accountable", "Non-Labor Cost", "Nuclear", "Nuclear Restricted", "Nuclear Restricted Reason", "Outgoing Meter Reading", "Outgoing Util Report Date", "Owning UIC", "PMC Total Time in Days", "PMC Total Time in Hours", "PMCB Days", "PMCB Hours", "PMCM Days", "PMCM Hours", "PMCS Days", "PMCS Hours", "Precious Metal Code", "Preservation Level Cd", "Prep for Shipment Code", "Rcpt Dt", "Reimbursable", "Remarks", "Requested Dt", "Required Delivery Dt", "Return Date", "RIC", "Sched Exists", "Site Id", "Special Instructions", "Storage Type Cd", "Total Actual Cost", "Total Est Cost", "Total Util Qty", "UII", "Work Order Reason"}, {"Work Order Id", "Sub Work Order Id", "Approval Dt", "Asset LIN/TAMCN", "Asset Id", "Item Desc", "Maint Activity", "Priority Cd", "Sub Work Order State Cd", "Work Order Status Cd", "Maint Team Id", "Service Performed", "Service End Dt", "DOD Serial Nbr", "ECC", "Serial Nbr", "Stock Nbr", "Doc Nbr", "Closed Dt", "Work Order Has Asset", "ACC", "Actual Labor Hours", "Asset Certifications", "Asset Location", "Asset UIC", "Avail Dt", "CAGE Code", "CIIC", "Cond Cd", "Custodian Id", "Dmil Cd", "Dollar Amount", "Equipment Pool", "Est Labor Cost", "Est Labor Hours", "Est Non-Labor Cost", "Est Service End Dt", "Estbd By", "Estbd Dt/Time", "Haz Mat Cd", "Incoming Meter Reading", "Incoming Util Report Date", "Job Order Nbr", "Labor Cost", "Last Updtd By", "Maint Loc", "Manufactured Year", "MC Status", "Meter", "Mfr Model Nbr", "Mfr Name", "Mfr Part Nbr", "Mission Critical", "NMC/PMC End Date", "NMC/PMC Start Date", "NMC Total Time in Days", "NMC Total Time in Hours", "NMCB Days", "NMCB Hours", "NMCM Days", "NMCM Hours", "NMCS Days", "NMCS Hours", "Non Accountable", "Non-Labor Cost", "Nuclear", "Nuclear Restricted", "Nuclear Restricted Reason", "Outgoing Meter Reading", "Outgoing Util Report Date", "Owning UIC", "PMC Total Time in Days", "PMC Total Time in Hours", "PMCB Days", "PMCB Hours", "PMCM Days", "PMCM Hours", "PMCS Days", "PMCS Hours", "Precious Metal Code", "Preservation Level Cd", "Prep for Shipment Code", "Rcpt Dt", "Reimbursable", "Remarks", "Requested Dt", "Required Delivery Dt", "Return Date", "RIC", "Sched Exists", "Site Id", "Special Instructions", "Storage Type Cd", "Total Actual Cost", "Total Est Cost", "Total Util Qty", "UII", "Work Order Reason"})As you can see, it is every single column from both tables, how can I adjust this so it pulls the information right? Again I don't know much, but some of the columns do have overlapping information. I'm not sure if there's a way to disregard certain columns from a table so it doesn't try and double tap the information?
FYI there's no overlap in your Expand:
=Table.ExpandTableColumn( Source, "Content", {"Work Order Id", "Sub Work Order Id", "Approval Dt", "Asset LIN/TAMCN", "Asset Id", "Item Desc", "Maint Activity", "Priority Cd", "Sub Work Order State Cd", "Work Order Status Cd", "Maint Team Id", "Service Performed", "Service End Dt", "DOD Serial Nbr", "ECC", "Serial Nbr", "Stock Nbr", "Doc Nbr", "Closed Dt", "Work Order Has Asset", "ACC", "Actual Labor Hours", "Asset Certifications", "Asset Location", "Asset UIC", "Avail Dt", "CAGE Code", "CIIC", "Cond Cd", "Custodian Id", "Dmil Cd", "Dollar Amount", "Equipment Pool", "Est Labor Cost", "Est Labor Hours", "Est Non-Labor Cost", "Est Service End Dt", "Estbd By", "Estbd Dt/Time", "Haz Mat Cd", "Incoming Meter Reading", "Incoming Util Report Date", "Job Order Nbr", "Labor Cost", "Last Updtd By", "Maint Loc", "Manufactured Year", "MC Status", "Meter", "Mfr Model Nbr", "Mfr Name", "Mfr Part Nbr", "Mission Critical", "NMC/PMC End Date", "NMC/PMC Start Date", "NMC Total Time in Days", "NMC Total Time in Hours", "NMCB Days", "NMCB Hours", "NMCM Days", "NMCM Hours", "NMCS Days", "NMCS Hours", "Non Accountable", "Non-Labor Cost", "Nuclear", "Nuclear Restricted", "Nuclear Restricted Reason", "Outgoing Meter Reading", "Outgoing Util Report Date", "Owning UIC", "PMC Total Time in Days", "PMC Total Time in Hours", "PMCB Days", "PMCB Hours", "PMCM Days", "PMCM Hours", "PMCS Days", "PMCS Hours", "Precious Metal Code", "Preservation Level Cd", "Prep for Shipment Code", "Rcpt Dt", "Reimbursable", "Remarks", "Requested Dt", "Required Delivery Dt", "Return Date", "RIC", "Sched Exists", "Site Id", "Special Instructions", "Storage Type Cd", "Total Actual Cost", "Total Est Cost", "Total Util Qty", "UII", "Work Order Reason"}, {"Work Order Id", "Sub Work Order Id", "Approval Dt", "Asset LIN/TAMCN", "Asset Id", "Item Desc", "Maint Activity", "Priority Cd", "Sub Work Order State Cd", "Work Order Status Cd", "Maint Team Id", "Service Performed", "Service End Dt", "DOD Serial Nbr", "ECC", "Serial Nbr", "Stock Nbr", "Doc Nbr", "Closed Dt", "Work Order Has Asset", "ACC", "Actual Labor Hours", "Asset Certifications", "Asset Location", "Asset UIC", "Avail Dt", "CAGE Code", "CIIC", "Cond Cd", "Custodian Id", "Dmil Cd", "Dollar Amount", "Equipment Pool", "Est Labor Cost", "Est Labor Hours", "Est Non-Labor Cost", "Est Service End Dt", "Estbd By", "Estbd Dt/Time", "Haz Mat Cd", "Incoming Meter Reading", "Incoming Util Report Date", "Job Order Nbr", "Labor Cost", "Last Updtd By", "Maint Loc", "Manufactured Year", "MC Status", "Meter", "Mfr Model Nbr", "Mfr Name", "Mfr Part Nbr", "Mission Critical", "NMC/PMC End Date", "NMC/PMC Start Date", "NMC Total Time in Days", "NMC Total Time in Hours", "NMCB Days", "NMCB Hours", "NMCM Days", "NMCM Hours", "NMCS Days", "NMCS Hours", "Non Accountable", "Non-Labor Cost", "Nuclear", "Nuclear Restricted", "Nuclear Restricted Reason", "Outgoing Meter Reading", "Outgoing Util Report Date", "Owning UIC", "PMC Total Time in Days", "PMC Total Time in Hours", "PMCB Days", "PMCB Hours", "PMCM Days", "PMCM Hours", "PMCS Days", "PMCS Hours", "Precious Metal Code", "Preservation Level Cd", "Prep for Shipment Code", "Rcpt Dt", "Reimbursable", "Remarks", "Requested Dt", "Required Delivery Dt", "Return Date", "RIC", "Sched Exists", "Site Id", "Special Instructions", "Storage Type Cd", "Total Actual Cost", "Total Est Cost", "Total Util Qty", "UII", "Work Order Reason"} )If you use the following instead you'll get the same result:
=Table.ExpandTableColumn( Source, "Content", {"Work Order Id", "Sub Work Order Id", "Approval Dt", "Asset LIN/TAMCN", "Asset Id", "Item Desc", "Maint Activity", "Priority Cd", "Sub Work Order State Cd", "Work Order Status Cd", "Maint Team Id", "Service Performed", "Service End Dt", "DOD Serial Nbr", "ECC", "Serial Nbr", "Stock Nbr", "Doc Nbr", "Closed Dt", "Work Order Has Asset", "ACC", "Actual Labor Hours", "Asset Certifications", "Asset Location", "Asset UIC", "Avail Dt", "CAGE Code", "CIIC", "Cond Cd", "Custodian Id", "Dmil Cd", "Dollar Amount", "Equipment Pool", "Est Labor Cost", "Est Labor Hours", "Est Non-Labor Cost", "Est Service End Dt", "Estbd By", "Estbd Dt/Time", "Haz Mat Cd", "Incoming Meter Reading", "Incoming Util Report Date", "Job Order Nbr", "Labor Cost", "Last Updtd By", "Maint Loc", "Manufactured Year", "MC Status", "Meter", "Mfr Model Nbr", "Mfr Name", "Mfr Part Nbr", "Mission Critical", "NMC/PMC End Date", "NMC/PMC Start Date", "NMC Total Time in Days", "NMC Total Time in Hours", "NMCB Days", "NMCB Hours", "NMCM Days", "NMCM Hours", "NMCS Days", "NMCS Hours", "Non Accountable", "Non-Labor Cost", "Nuclear", "Nuclear Restricted", "Nuclear Restricted Reason", "Outgoing Meter Reading", "Outgoing Util Report Date", "Owning UIC", "PMC Total Time in Days", "PMC Total Time in Hours", "PMCB Days", "PMCB Hours", "PMCM Days", "PMCM Hours", "PMCS Days", "PMCS Hours", "Precious Metal Code", "Preservation Level Cd", "Prep for Shipment Code", "Rcpt Dt", "Reimbursable", "Remarks", "Requested Dt", "Required Delivery Dt", "Return Date", "RIC", "Sched Exists", "Site Id", "Special Instructions", "Storage Type Cd", "Total Actual Cost", "Total Est Cost", "Total Util Qty", "UII", "Work Order Reason"} )The 2nd list {...} allows you to rename all/some fields when expanding. If you have no need to do that you can use a single list {...} of fields to expand as shown above
Table.ExpandTableColumn doc.: Table.ExpandTableColumn - PowerQuery M | Microsoft Learn
