new query how to remap all functions or just do by hand

[ElliotP]

Afternoon,

I have to create some new queries to bring pretty much the same data (columns and data type) into my powerbi file as I'm now getting my data from an sql table and not my csv anymore. Is there an easy way to swap over my query as so all my measures and tables which will be populated by the same columns swap over? as opposed to individually having to create each measure again in the new table with the new table name?

I feel like if I were to change some of the power query in the editor I might be able to achieve this.

[Phil_Seamark]

I've done this in the past using the Advanced Editor in the Query Editor.  It's quite fiddly and I recommend you backup the text in this editor before you start.

Then if you bring your new data in to different tables, you can work out what is required to change these over.

It is fiddly, but it is possible.  I don't believe a tool exists yet to easily do this.

[ElliotP]

Having a look at the advanced editor, it seems the simplest option might be the best way, but admittedly its super basic and might be an issue.

You could just literally title the table the same name as the previous table, just have it coming from a new query; assuming column titles are the same, it might work?

[Phil_Seamark]

Yeah, having the column names the same really helps, as well as understanding how the M logic works.  But if the column names are slightly different, you can still achieve what you are trying to do, just have a good coffee before you start 😉

[ElliotP]

part of me is wondering if I might just have to suck it up and literally just copy and paste and edit all of the measures/etc for each visual and do it one by one. I'd really love to avoid that, but it might be the best option and then once it's done it's done forever.

[Phil_Seamark]

If you retrofit the top lines using the Advanced Editor, then any visuals you currently have using that table will continue to work.  If you have lots of these, this will save you alot of time! 🙂

[ElliotP]

What do you mean? retrofit

[Phil_Seamark]

Overwrite the top lines of your Advanced Editor with the updated data source commands.

[ImkeF]

I'd suggest the following:

1) Create a new query which imports your data from your new source and returns the same data as your old query

2) As a last step, rename all your column names according to the column names in your previous query (consider this as your manual semantic layer)

3) Copy the M-code of this query from the advanced editor

4) Open the advanced editor of your old existing query and paste the new code into it:

Everything should work as before, just that the data now comes from the new source.

You can then delete your "new" query, as you've only used this to create the new query code (which now sits in your old query).

[ElliotP]

That sounds like the best solution going forward. I'll let you know how it works out.

[v-sihou-msft]

@ElliotP

Since your sql table has same metadata as the CSV file, you can just replace the entire Power Query with the new Power Query (retrieve data from SQL Server). It can change the source for this dataset and remap all created measures and columns properly.

Replace it with new M query.

The raw data change to new one.