Change data source of a chart

[Nestuser]

Hello all!

I want to duplicate a chart but changing the data source from inside the same spreadsheet (different sheets, same table formats). Where do I find this option? i have found change data source but i can't arrive to select the table or sheet i want.

Thank u so much!

[v-xuding-msft · Accepted solution]

Hi @Nestuser ,

There isn’t an option to change sheets from the data sources directly in Power BI. There is a way which could implement it using parameters in Power Query.  In this way, you have to make the format of each sheet you want to change the same, like the number and type of columns . Then, while changing the value of  parameters, the item of source will be changed automatically. 

1. Copy and paste Table 1 in Power Query .

1. Click Home --> Manage Parameters -->New --> Choose the values of Sheet  you want to change.

1. Right click Table1(2) -->choose Advanced Editor ---> change the value of Item equals parameter you created.

The Item is using parameters you created and the kind is changed  to “Sheet”.

1. Remove the top row, then you get the result.  Click Home-->Remove Rows

There is the M code you can reference.

let

    Source = Excel.Workbook(File.Contents("D:\case\change data sources.xlsx"), null, true),

    Table1_Table = Source{[Item=#"data sources",Kind="Sheet"]}[Data],

    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Column1", type date}, {"Column2", type date}, {"Column3", type datetime}, {"Column4", type date}, {"Column5", type datetime}}),

    #"Removed Top Rows" = Table.Skip(#"Changed Type",1)

in

    #"Removed Top Rows"

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.