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 all the fields were named the same.

 

However when importing I lost all the calculated columns.

Has anyone experienced this?

 

I have done this before swapping excel sources but never excel - SQL.

 

I guess it could be the table name that is different from the sheet name in the excel but I don't really know how to get around that.

 

Thank you for your help.

 

Quentin

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 

6 Replies

  • 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