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.
Resolver IIHi CharlieN
The issue arises from utilizing two separate imports of the same combined_ship table, renamed as schip (via rapport) and schip (niet via rapport), each linked to different segments of the data model. This configuration leads to redundancy and ambiguity in relationships, complicating the accurate resolution of ship names in visuals.
The optimal solution is to eliminate the duplication by importing the combined_ship table only once. This single import should serve as a central lookup table containing all unique Ship IDs and their names. Subsequently, establish relationships from both braboejob_report[ShipID] and braboejob_combined_order[ShipID] to the unified combined_ship[ShipID] table.
As Power BI permits only one active relationship between tables, one relationship should remain inactive. To resolve ship names contextually within visuals, a DAX measure utilizing the USERELATIONSHIP function can dynamically activate the appropriate relationship based on whether the visual uses data from the report or the order table. The measure employs CALCULATE with USERELATIONSHIP for both tables and returns the first available ship name using COALESCE.
This approach maintains a clean data model, avoids redundancy, and ensures precise ship name resolution without requiring duplicate tables or ambiguous joins. Optionally, for enhanced simplicity, the user can merge the two fact tables (braboejob_report and braboejob_combined_order) in Power Query and add a source indicator column, further streamlining reporting and DAX calculations.
Hi ABD128,
I'm trying the Power Query route here, only not clear to me how to add such a source indicator column..
I get the concept that I have to choose the ship whether it's coming via the report table, or via the combined_order table, but how to exactly introduce such a column in the merged table? From there on I can continue my quest, I hope...
Sorry, just a (DAX & M) rookie here.
Thanks again,
KR,
CharlieN
v-karpurapudCommunity Support
Hi CharlieN
We are following up once again regarding your query. Could you confirm whether your query has been resolved? If so, kindly mark the helpful response and accept it as the solution to assist other community members in resolving similar issues more efficiently.
Thank You.