Forum Discussion

qmartiny's avatar
qmartiny
Icon for Helper III rankHelper III
4 years ago
Solved

Changing data source from excel to SQL without losing calculated columns?

Hello all, I have a query in a PowerBI that combined multiple excel files into 1.  Recently I got the access to the SQL server so I switched the query source to use the SQL instead. I made sure ...
Need Help
  • jbwtp's avatar
    jbwtp
    4 years ago

    Hi qmartiny,

     

    This is hard to say without having the actual model to play with, but I would doubt so. In my experience, this never happend. Not that I've done it million times, but quite a number of times.

     

    Technically, losing of the calculated columns should not also occur in the normal way of business if the table name of the SQL version of the query is exactly the same as the old one (Excel-version of the query). The PBI is presumably agnostic of anything what happens on the query side and only deal with the output.

    In the wort case scenario you could loose relationships and/or calculated columns. It should not really freeze the import. Would this be possible to verify your assumption to delete all the relationships to the table (in a copy of the model) and see if it loads Ok?

    Could you please also check that not just the column names, but also the data types are the same as in the original table?

     

    Good luck, 

    John 

jbwtp's avatar
jbwtp
Icon for Memorable Member rankMemorable Member
4 years ago

Hi qmartiny,

 

I would suggest to build the SQL query as a separate query in PQ, disable loading to PBI for it.

In the original query that feeds to your table in the PBI Designer: remove everything except one step and make it pointing to your new SQL query. This would mask for PBI that something has changed on the background, so the calcualted columns and measures sould not disappear.

 

Kind regards,

John

  • qmartiny's avatar
    qmartiny
    Icon for Helper III rankHelper III
    4 years ago

    Hi John,


    Thanks a lot for this ! 

    It looked promising.

    I did as you said - reference the query + keep the last step ( a simple "remove nulls")

     

    However I'm getting the dreaded infinite 'loading' time. I tried just the SQL query by itself and it loaded fine.

    I made sure all the columns were exactly the same with no extra columns. 

     

    Any ideas why this could be happening still?

     

     

    • jbwtp's avatar
      jbwtp
      Icon for Memorable Member rankMemorable Member
      4 years ago

      Hi qmartiny,

       

      In this case, your "old" query should look something like this:

      let
    Source = MyNewSQLQuery,
    #"Removed Blank Rows" = Table.SelectRows(MyNewSQLQuery, each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

       

      Can you rewrite it to look like this:

      let
    Source = MyNewSQLQuery
in Source

      This theoretically will make it passing through the SQL query without any modifications and should be execat same as running the SQL query itself.

       

      See if this improves performance and then experiement with adding more transsformations (e.g. RemoveNulls).

       

      Cheers,

      John

       

      • qmartiny's avatar
        qmartiny
        Icon for Helper III rankHelper III
        4 years ago

        Hi John,

         

        I'm still getting infinite loading. I tried to increase the number of simultaneous evaluations but no change.
        I think the model might be too complex still.

         

        I'm guessing it has to do with the existing relationships + calculated tables..

         

        Thanks for your help anyway