New Appended Query issue/question

[jbrines]

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

[MichaelSamiotis]

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.

[jbrines]

Hi @MichaelSamiotis clearing cache didn't resolve it.

Any articles on how to use refreencing?

Thanks

J

[MichaelSamiotis]

Have a look on that one please: https://support.microsoft.com/en-gb/office/combine-multiple-queries-power-query-16b1421c-9708-466a-8...

[jbrines]

Hi @MichaelSamiotis so basically referencing is merging?

[MichaelSamiotis]

Exactly. Another thing to try if this doesn't work is to remove any extra steps from your individual tables before appending. All the transformation steps should be on the final query.

[jbrines]

Hi @MichaelSamiotis I don't think merge will work for me as I don't want the data to the side of each other I want it below it if you know what I mean.

So each query bring data from a sharepoint list each list has the same colums Name, Company, Profession etc and I want to put them in to one big table.

Maybe there is another way of doing this instead of appending and merging?

Thanks

J

[MichaelSamiotis]

Hi,

I didn;t mention merge, only appending the queries. You can do it by going to Get Data > File > SharePoint Folder

If this doesn;t work, can you please share the file to have a look?

[jbrines]

Hi @MichaelSamiotis Is it fine to share on here or can I share it another way?

[MichaelSamiotis]

Hi, just sent you a PM.

[jbrines]

I only have a few steps in each query and they all have the same steps. See below.

    #"Replaced Value" = Table.ReplaceValue(#"Expanded value",null,"CE4079",Replacer.ReplaceValue,{"value.Contract"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"value.Modified", type datetime}, {"value.Created", type datetime}, {"value.SignInDateandTime", type datetime}, {"value.SignOutDateandTime", type datetime}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"value.Title", "Name"}, {"value.Modified", "Modified"}, {"value.Created", "Created"}, {"value.Company", "Company"}, {"value.SignInDateandTime", "Sign In Date and Time"}, {"value.SignOutDateandTime", "Sign Out Date and Time"}, {"value.Profession", "Profession"}, {"value.SignInLocation", "Sign In Location"}, {"value.SignOutLocation", "Sign Out Location"}, {"value.ModeofTransport", "Mode of Transport"}, {"value.AreyouaDriver_x002f_Passenger", "Are you a Driver or Passenger"}, {"value.PostcodeStart", "Postcode Start"}, {"value.ProjectPostcode", "Project Postcode"}, {"value.Distance_x0028_includingreturnjo", "Distance includingr eturn journey"}, {"value.Contract", "Contract"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Skip"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Distance includingr eturn journey", "Distance including return journey"}, {"value.Created_x0020_Date0", "Created Date"}, {"value.Created_x0020_Time", "Created Time"}, {"value.Modified_x0020_Time", "Modified Time"}, {"value.Sign_x0020_Out_x0020_Time", "Sign Out Time"}, {"value.Hours", "Hours"}})

 Would it be OK to keep them in? 

[MichaelSamiotis]

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]

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]

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.