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