May 7, 2020
Please see my issue and help guide me to a solution.
Goal: Get desired table result in form of columns of table 3 based on joins explained below
1.To perform join b/w one column table 1 and table 2
2.Perform join b/w table 2 and table 3 based on two column which have duplicate email address
3.Result a table rows of table 3 which are matching and un-matching so the team can view
1.Imported the tables 1 and 2,Performed cleaning,trimming , separated by delimiter and merging of col for dash values and spaces sperately,Loaded the data in to seperate sheets for Goal 1. Performed merge 1 using inner join selecting each col from table 1 and table 2
ERROR:Dataformat.Error:We couldn't convert into number
2.Goal 1 works after adding the table to datamodel and creating relationship.
3.For Goal 2 the add to datamodel,data is duplicate email addresses so gives an error that relationship can't be created as the coloumn has duplicate values and need unique values to create relationship.
Please suggest which way to follow and how to understand joins,data modelling better using power queries ?
July 16, 2010
Welcome to our forum! It's difficult to help without seeing your data.
The error is caused by text in a column that should only contain numbers. You need to clean the text before merging. This post explains the different Power Query Join types.
Power Pivot (Data Model) will not allow you to create a relationship between two tables that both contain duplicates. You need to create a third dimension table that contains the unique list and create relationships from your two tables, to the dimension table. You then use the field from the dimension table in your PivotTable row/column labels.
I hope that points you in the right direction.