Forum Discussion
Table connection
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?
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.
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