Table connection

[Gandhia]

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

[Gandhia · Accepted solution]

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?

[divyed]

Hello @Gandhia ,

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

[Gandhia]

I think this doesn't work, if there are different amount of rows and columns? If 2022 there are 500 rows and 30 questions and 2023 400 rows and 25 questions. 

[divyed]

Hello @Gandhia ,

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/ 

[Gandhia]

Thank you! Really appreciate it. But I don't have one "Question" column which has all questions, I have a lot of columns like "Question X (where X is 1, 2, 3, 4, ..., x)". So it doesn't answer fully on this situations, does it? Sorry if I understand you wrong 😄

Thank you in advance

[Tahreem24]

@Gandhia 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. 

[Gandhia]

Thank you for your response!

I created two sample tables, I don't know if they can help but let's see 🙂

There can ofc be a lot of more rows and columns in the real data and tables.

See the photo below (BUT there are also different Questions etc between tables(

•  

[Selva-Salimi]

Hi @Gandhia 

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.

[Gandhia]

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

[v-xinruzhu-msft]

Hi,

Thanks for the solution Tahreem24 and Selva-Salimi  offered, and i want to offer some more information for user to refer to.

hello @Gandhia , 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.

[Gandhia]

Thank you! Do you think this is suitable solution, although there would be for example 30 or 50 dirreferent questions in each year? I think then there would be a lot or rows... And of course columns also because one question is going to multiple 2 (Question & value). 

Is it anyway the best workaround? Or is there any other options?

- G