Change Report's Data Source

[rds_2019]

Hi All,

My Report is using Excel Table as a Data Source. After processing i have to create another Appended Table, which i would like to use as a data source

How can i change current Data source excel to another Appenede Table(source for this appaended table is 2 excel sheets).

Is it possible?

Regards,

RDS

[jdbuchanan71]

Hello @rds_2019 

Your model only cares about what comes out of PowerQuery, not all the steps that PowerQuery goes through to get there.  What you want to do is create a table using your new data source, the append query, and make sure it's output has the EXACT same columns as your old table.  Sames names exatly ("column" is not the same as "Column") and same data types.

Once you have that you can open your new query in advanced editor and copy out all the code.  Then open your old query in advanced editor and replace all the code with the code from your new source.

[rds_2019]

Hi @jdbuchanan71  ,

Thanks for replying, how can add Appended Table as a source to the New Table coz Table created by Appending 2 excels doesnt show up in the browser to be added as a source.

RDS

[jdbuchanan71]

You would pull in each file as it's own query then combine them in an append query and make the output of the combined append the source you send into the model.

[rds_2019]

Hi @jdbuchanan71 

I tried to understand what is written by you but dont know how to do it

Let me clarify my question further .

I am using Table1 which is an excel table and have created Dashboard based on it.

Now the data sources has increased so I have created 

Append__Table1 by adding 2 different Excels= Table3 Appended with Table4.

No I want to replace data source of Dashboard using Table1 to Append_Table1.

I have got 2 ways to do it as per my understanding.

1.Make changes to query of Table1 and make it refer to Append_Table(Since Latest table is Appended Table , I cant just replace to it since it doesnt show up as an option)

2.Replace Data Source of Dashboard to another Table -Appened_Table1

So which would be right way to do it and how to get it done?

regards,

RDS

[v-lid-msft · Accepted solution]

Hi @rds_2019 ,

Based on your describle, We did some tested on it, your first solution should works fine, just change the query of table1 to the following in the Advanced Editor, But never forget to make backup of pbix file.

Append__Table1

After reference to the append table, all your visual and measure releated to old table will still exist.

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rds_2019]

Thanks @v-lid-msft  this was simple, simple reference to the APPEND_TABL1. didnt get any option so wasnt sure how to refer to the table which is not physical and not showing up in the browser

[jdbuchanan71]

@rds_2019 

Can you share you .pbix file so I can see the step you have applied and the layout of the tables?

[kentyler]

Are the old table and the new table identical in structure, same field names, data types and everything ?

If so you can use "Duplicate" to make an exact copy of the old table, and then change its data source property to the new table. This post gives good instructions https://radacad.com/reference-vs-duplicate-in-power-bi-power-query-back-to-basics

[rds_2019]

Hi,

I cant change the data source , coz the existing data source is from Excel Table  and the data source i want to replace to is Appeneded Table, both are in exactly same in number of columns and data type etc. Since for change in Data source its more related to actual Physical Table where as Table which are created in Power BI by appeneding 2 more more tables are not physical  so doesnt show up in the browsing window while changing the Data Source.

Regards,

RDS