Create a new table through sql query using the existing table that was loaded via excelsheet

[pshetty05]

Hello,

I have table A that has 2 values - Email address & Client Status which has been created using an excelsheet.

Now, I want to create another table that uses the values from this table to create another table B which will now have few more columns like Title, Role etc. along with Email address & Client Status using power query.

Earlier I used to create a temp table in MS SQL for table A and would use it in the Query Editor to create table B but when I already have the data of Table A in Power BI, I want to eradicate use of MS SQL.

My query is as follows - 

select Email Address, Title, Role, Client Status from Table A, Table B where A.email = B.email

How do I use Table A in the power query?

[amitchandak]

@pshetty05 , try https://radacad.com/append-vs-merge-in-power-bi-and-power-query

or

https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/

[amitchandak]

@pshetty05 , Missed your last update. Option in edit query

Copy a table and remove column not need and remove duplicates

Select the required column, Right-click , last option add as new query and remove duplicates, convert to tables.

In DAX, you selectcoulmns, summarize, summarizecolumns

[PaulDBrown]

@pshetty05 

I believe that what you need to do is  merge the tables in Power Query

[pshetty05]

Hi Paul, 

I want to create Table B.

Merge is possible for the tables that are already present. 

[PaulDBrown]

@pshetty05 

Sorry, I misunderstood your question. 
what is the source for the extra columns? SQL? If so, import and merge.
If not, it sounds to me you are better off creating the whole table in Excel. (Or create another table with the extra columns in Excel and then merge them, if you are only updating the second part). I find it hard to see the benefits of doing this in Power Query

[pshetty05]

Hi Paul, Amit,

Thank you for your prompt response 🙂 I really appreciate it.

Let me elaborate my requirement further - I have two sources A) Eloqua B) SQL

So, my table A is formed by exporting columns (Email Address & Client Status) from Eloqua and then creating a temp table in SQL.

Table B is created in SLQ using the Email Address from Table A and other tables which has columns only present in SLQ.

Now, using this query I find out the MIN and MAX Open Dates of contacts based on their status.

Requirement - Since this is too much of a process, I'm planning to move it to Power BI. I can export columns from Eloqua which in turn can be automatically exported into PowerBI by using the path as parameter but the next steps of using Table A in SQL to create Table B is where I'm stuck. Once my Table B gets created in PBI itself using Table A and then finding the MIN and MAX Open Dates of contacts based on their status can be done using DAX.

I'm looking for a solution where I can write a query which joins with Table A that is created in PBI.

eg. "Table A" inner join [SQL.table]

Just like how we can pass a parameter in the query. 

My SQL table is very huge and therefore while exporting this table, my PBI fails. Therefore, I'm unable to use the Merge Function. Let me know if you have any further questions.

[v-rzhou-msft]

Hi @pshetty05 

When you import data from SQL, you may try to use SQL statement in advanced options to optimize your data model.

For more info about optimizing the data model: Optimization guide for Power BI

And then you can use merge to build a new table.

I build a sample table.

Then build two measures to calculate the max and min OpenDate to each Statue.

MinOpenDate = MINX(FILTER(ALL('Table'),'Table'[Statues]=MAX('Table'[Statues])),'Table'[OpenDate])

MaxOpenDate = MAXX(FILTER(ALL('Table'),'Table'[Statues]=MAX('Table'[Statues])),'Table'[OpenDate])

Result:

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[pshetty05]

Hi Rico,

Thank you for the detailed solution.

I tried this one but the load fails as there is huge data in the Contacts table.

Therefore, while writing the query of "Select *.." is there a way to use the existing table in the PBI as the main table and left join it with Contacts tbl to load lesser data.