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 th...
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