Forum Discussion

jbrines's avatar
jbrines
Advocate III
1 year ago

New Appended Query issue/question

Hi Guys,

 

I have created a new appended query that has over 30 queries in it.

 

With the new appended query named "All Data" I then start using commands like Trim, Change Type and Sorting Rows. When I add a number of replace values to the "All Data" Query and Close and Apply then some if not most of the replaced values are still there. I am only selecting columns from the "All Data" Query which doesn't have these values.

 

So it looks like it is using all the data from all the queries and not just the "All Data" query.

 

How do I resolve this please/

 

Thanks

 

J

Need Help

13 Replies

  • MichaelSamiotis's avatar
    MichaelSamiotis
    Resolver I
    1 year ago

    Power BI might still be referencing the original queries instead of just using the "All Data" query.

     

    I would make sure that "All Data" is the only query being used in your report through the Query Dependencies in Power Query. If other queries are still being referenced, Power BI might be pulling data from them.

     

    Another thing to check is whether the original 30+ queries are still loading into the model. In Power Query, right-click each query and uncheck "Enable Load" unless you actually need them in the report.

     

    If Power BI is still showing old data, try forcing it to recalculate by Refresh Preview in Power Query and the Close & Apply again. Also, o to Transform Data => Data Source Settings, clear the cache, and refresh.

     

    My last attempt would be to try using a Reference instead of Append.

     

    Hope that helps! 😊

    • jbrines's avatar
      jbrines
      Advocate III
      1 year ago

      Hi MichaelSamiotis 

       

      Thanks for replying.

       

      All Data has all the other queries in the Query Dependencies in Power Query.

       

      I am pulling all the data from the other queries so I believe I need to keep enable Load.

       

      When I go in to Transform Data => Data Source Settings, clear the cache isn't there.

       

      Will using Reference give me all the data in one table?

       

      Thanks

       

      J

      • MichaelSamiotis's avatar
        MichaelSamiotis
        Resolver I
        1 year ago

        When appending multiple queries, Power BI sometimes keeps references to the original queries, especially if transformations (like Replace Values) are applied before appending. This can lead to unexpected behaviour where changes don't fully apply.

        Instead of appending and modifying within the same query, try referencing the "All Data" query and applying transformations separately.

        1. Create the Appended Query:

          • Append all 30 queries together into "All Data".
          • Do not apply transformations (Trim, Change Type, Replace Values) inside this query.

        2. Create a Reference to "All Data":

          • In Power Query, right-click "All Data" and choose Reference.
          • This creates a new query that only refers to "All Data" instead of directly modifying it.

        3. Apply Transformations in the Referenced Query:

          • Now apply Trim, Change Type, Replace Values, Sorting Rows, etc., in this new query.
          • This prevents Power BI from re-evaluating the original queries when making changes.

        4. Load Only the Referenced Query:

          • Go back to Power Query.
          • Right-click the original 30 queries and uncheck "Enable Load" so they don’t get loaded into the model.
          • Keep only the referenced query loading into Power BI.

        Let's try this and see what it does.




  • MichaelSamiotis's avatar
    MichaelSamiotis
    Resolver I
    1 year ago

    Enable load means that everything will be loaded inot the data model. You can find the clear cache in Options > Data Load . Clear Cache.

     

    Reference data just references the tables, insted of duplicating them into a new one.