Forum Discussion

pshetty05's avatar
pshetty05
Helper I
6 years ago

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

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...
Need Help
PaulDBrown's avatar
PaulDBrown
Community Champion
6 years ago

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's avatar
pshetty05
Helper I
6 years ago

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.

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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's avatar
      pshetty05
      Helper I
      6 years ago

      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.