Is there a way to remove the Query prefix from column names?

[PowerAutomater]

I have created a table visualisation in Power Bi Desktop. I noticed that when hovering on the field there is a prefix of "Query1" which is what the data source has been named:

I have then added a Power Automate button/visualiser and created a flow that extracts the data and creates a csv file. The problem is though that instead of using the original columns names as the column headers, the csv file has this same "Query 1" prefix:

Is there a way to remove this prefix in PowerBI, so the flow doesn't use it when creating the csv?

[danextian · Accepted solution]

Hi @PowerAutomater 

In the EVALUATE clause, you can use SELECTCOLUMNS to select columns from the prior tables in your query, This will remove the table names but keep the square brackets

If you want the square brackets removed, it's more of a Power Automate task than Power BI. You need to add a select step after Run a query against a dataset step use dynamic expressions.

[Anonymous]

Hi @PowerAutomater,

Thanks for the reply from shafiz_p.

Renaming directly is good if your have fewer column names to deal with, if you have multiple columns, I suggest you use the Advanced Editor to use Table.TransformColumnNames.

Write the M query as below:

#"Renamed Columns" = Table.TransformColumnNames(#"Previous Step", each Text.Replace(_, "Query1.", ""))

Here is what I have in my editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srMVfDNLypJzStOzVPSUTIyMDIGUoZQpq6BIRApxeoAVSbmpSq45KfiUGQEUYTdOCNklRZglY45mcmpCsG5mSUZONRZ4rDWGEmRoSlYkVN+koJXfkZecX4eDnVm+JyHotIcn0oTJJVGQC/HAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CustomerID = _t, Year = _t, WeekNumber = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", type text}, {"Year", Int64.Type}, {"WeekNumber", Int64.Type}, {"Date", type date}}),
    #"Renamed Columns" = Table.TransformColumnNames(#"Changed Type", each Text.Replace(_, "Query1.", "")) // This removes any "Query1." prefix from column names
in
    #"Renamed Columns"

Once you’ve cleaned up the column names in Power Query, verify that the flow you’ve set up in Power Automate uses the correct headers when exporting to CSV.

If you have any other questions please feel free to contact me.

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[shafiz_p]

Hi @PowerAutomater 

You can open power query and rename column.

or

Use power automate to rename column after creating csv. 

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,
Shahariar Hafiz