Forum Discussion
Merge two same columns from twice imported tables in table visual
Hi CharlieN
To consolidate data from the braboejob_report and braboejob_combined_order tables using Power Query, begin by adding a source identifier to each table. In Power Query Editor, select the braboejob_report table, navigate to the Add Column tab, and insert a Custom Column named "Source" with the fixed value "Report". Repeat this process for the braboejob_combined_order table, assigning the value "Order" to the "Source" column. Once both tables are tagged with their respective origins, go to the Home tab and select Append Queries as New. Choose to append the two tables, which will generate a new unified query containing all rows from both sources along with their origin labels. Rename this resulting query to something descriptive, such as "All_Jobs_With_Source", and load it into the data model. This combined dataset enables clear traceability of each record’s origin and simplifies downstream analysis, including DAX calculations and visualizations particularly for resolving ship names and performing contextual comparisons.
Super UserHi CharlieN ,
To merge the values from two columns that come from twice-imported versions of the same table in your table visual, you have a couple of options depending on your data model and your needs:
1. Create a Calculated Column (DAX)
If the columns you want to merge are in the same table visual but come from different tables (e.g., [ShipName1] from Table1 and [ShipName2] from Table2), you can create a calculated column or a DAX measure in Power BI that merges these two columns:
If you want to show the first non-blank value:
MergedShipName =
IF(
NOT(ISBLANK(Table1[ShipName1])),
Table1[ShipName1],
Table2[ShipName2]
)You can adapt this logic based on which value you want to prioritize.
2. Use Power Query to Merge Before Loading
If possible, merge the two tables in Power Query before they are loaded into the model. You can use “Append Queries” to stack the two tables or “Merge Queries” to join on a common key, and then create a single column.
3. Adjust Relationships
If the only way to get the names is to keep both tables, make sure your relationships are set correctly. You could create a new table (e.g., Combined_Ships) with unique Ship names and link both tables to this one via relationships. Then, you can always pull the ship name from the central table, avoiding duplicate columns.
4. In the Table Visual
If you must display both columns in the same visual and want to visually merge them, you can add both columns to the table and use a DAX measure or calculated column (as above) to display a single merged value.
Summary:
- Use a DAX calculated column or measure to merge the columns in your visual.
- For a more robust solution, consider restructuring your data model so both tables reference a single ship name lookup table.
- Power Query can also help if you want to merge the tables before loading.
If you describe more about your data model or share a sample, I can give a more targeted solution. Let me know if you need a step-by-step guide for any approach!
translation and formatting supported by AI
Hi burakkaragoz,
For the moment I'm not getting there.. Since the ship names are not unique (the id's are) it says for the DAX calculated column...
Also merging in PQ doesn't give me the expected result, my visual doesn't seem to pick-up the relationship with the newly added (combined) table - because it does not know which one of the two with same id to pick I presume...
In essence a ship (from table 'combined_ship') can appear multiple times with the same name, but each occurence has a different id.
Updated pbix: https://www.icloud.com/iclouddrive/0af-fAQKBFSebshgXYUQxNNog#ejob%5Fincidents%5Ffindshipname_2
Thanks again for your input and help.
KR,
CharlieN