Forum Discussion
Table connection
Hello, what are the solutions to connect 2 tables, when there aren't any unique similar columns. If you have e.g. two survey data from different years (2022 and 2023). If there are some same questions but no anything unique keys. Is it possible to connect them and build relationships? If you need to build report visualizations in the same page and in the same visualizations, you need connection between tables.
Thank you on advance,
G
It seems I solved the problem, there is a solution which seems like to work (this is anonymised and sample):
Combined_Data = UNION( SELECTCOLUMNS('Table1', "Year", 'Table1'[Year], "Question1", 'Table1'[Question1], "Question3", 'Table1'[Question3], "Customer Loyalty", 'Table1'[Readiness to recommend]), SELECTCOLUMNS('Table2', "Year", 'Table2'[Year], "Question1", 'Table2'[Question1], "Question3", 'Table2'[Question3], "Customer Loyalty", 'Table2'[Readiness to recommend]) )
So I made a new table that includes the column I need from both tables. Do you think there can be some problems or limitations with this solution / way? Or does it looks like a decent or good workaround?
11 Replies
Hi Anonymous
I recommend to unpivot your tables to have columns like (year, question , answer) and then union them, instead of builing relationships. this solution can be more efficient.
If this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.
Hi Selva-Salimi and thank for your response! Really appreciate it.
What do you exactly mean, do you mean that "unpivot" them different tables: Table1: Year, Table2: Answer, Table3: Other things like company name, name, role, title, address etcetc. whatever
Btw I don't have questions there, just answers. Questions were in the other places (like surveys) if it does matter.
And if I merge (or "union" like u said, is it a same thing?) them (tables), does they still refresh like before or does it affect it in the negative way? 🙂
Thank y'all for helping 🙂
- G
Hi,
Thanks for the solution Tahreem24 and Selva-Salimi offered, and i want to offer some more information for user to refer to.
hello Anonymous , based on the sample data, you can unpivot the tables to the following format in power query first.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUfJKTM5WCAIyzIHYDEobGRgZKcXqRCs5gVTkZ+QpeAAZFlCMosIZyPFNLClR8EdSYYaswgXIcU7My8svUggAMk2hJpgiq3EFmZJflJ6YBzYHpN8SiiFqYgE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company Name" = _t, Employer = _t, Question1 = _t, Questoin2 = _t, Question3 = _t, Year = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Name", type text}, {"Employer", type text}, {"Question1", Int64.Type}, {"Questoin2", Int64.Type}, {"Question3", Int64.Type}, {"Year", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company Name", "Employer", "Year"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Questions"}}) in #"Renamed Columns"let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclPSUfIpzU5VcAQyzIDYHIgtgNjIwMhEKVYnWskdrCI5UcEVyDCFqrBEVuEB5Hil5uVlpqUWgc2xRDILrsoTyHFOLElVcIKqgKmCq/ACcnwTS0oUXIAMQyQMURELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Company Name" = _t, Employer = _t, Question1 = _t, Questoin2 = _t, Question3 = _t, Year = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Company Name", type text}, {"Employer", type text}, {"Question1", Int64.Type}, {"Questoin2", Int64.Type}, {"Question3", Int64.Type}, {"Year", Int64.Type}}), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Company Name", "Employer", "Year"}, "Attribute", "Value"), #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Questions"}}) in #"Renamed Columns"2. You can create a calculated column.
QuestionValue = DISTINCT(UNION(VALUES('Table'[Value]),VALUES('Table (2)'[Value])))3.Then you can create the relationship among the tables(key field: value field in three tables)
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous Could you create a dummy sample data of your scenario (just 4-5 rows are enough)? So, It would be easy to understand your data pattern and structure and accordingly I could provide some workaround.
Hello Anonymous ,
Create a row_number column in both the tables based on "Questions" and use this row_number column to join tables.
1. It will connect both the tables based on questions
2. You can apply different type of joins (while merging) to cover all data.
I hope this helps.
Please mark this as solution if this has solved your query. Appreciate Kudos always :).
Cheers
Hello Anonymous ,
You can use Partition by to generate sequesnce for each questions which can than be used to join irrespective of number of rows.
Alternatively you can merge both the tables simply
1. Merge both the table based on "Question".
2. select "Question" as join column and choose appropriate join type. Click ok
3. Expand table to include columns you want
I hope this helps.
Cheers
Neeraj Kumar
https://www.linkedin.com/in/neeraj-kumar-62246b26/
It seems I solved the problem, there is a solution which seems like to work (this is anonymised and sample):
Combined_Data = UNION( SELECTCOLUMNS('Table1', "Year", 'Table1'[Year], "Question1", 'Table1'[Question1], "Question3", 'Table1'[Question3], "Customer Loyalty", 'Table1'[Readiness to recommend]), SELECTCOLUMNS('Table2', "Year", 'Table2'[Year], "Question1", 'Table2'[Question1], "Question3", 'Table2'[Question3], "Customer Loyalty", 'Table2'[Readiness to recommend]) )
So I made a new table that includes the column I need from both tables. Do you think there can be some problems or limitations with this solution / way? Or does it looks like a decent or good workaround?
