Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
krixtsup3r
Helper V
Helper V

Change/Remove Data Source

Hi All,

 

Is there a way to change the data source to a table? I have a table named 'hourly_data_new' the data source is from my Excel file. Now, I transitioned the data source to an email attachment which I receive daily then appends it to hourl_data_new table.

 

My problem is that the Grouped Tables that I created is not getting updated  with the new data that comes from email attachment. So I'm wondering if it's possible to change the source of excel file into table itself. 

 

Below is my query for a Grouped Table:

let
Source = Excel.Workbook(File.Contents("C:\Users\user2\Data Dump\Report Central\hourly_raw.xlsx"), null, true),
Table1_Table = Source{[Item="hourly_new_raw",Kind="Sheet"]}[Data],
#"Promoted Headers" = Table.PromoteHeaders(Table1_Table, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Project", type text},{"actual_date_interval", type date}, {"channel", type text}, {"units_completed", Int64.Type}, {"total_assignments", Int64.Type}, {"hours_worked", type number}}),
#"Grouped Rows" = Table.Group(#"Changed Type", {"actual_date_interval", "channel", "Project"}, {{"units_completed", each List.Sum([units_completed]), type number}, {"total_assignments", each List.Sum([total_assignments]), type number}, {"hours_worked", each List.Sum([hours_worked]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"actual_date_interval", Order.Descending}})
in
#"Sorted Rows"

 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @krixtsup3r ,

 

In my work with PBI I have add several times to change the datasource and in my experience there are some things that went good and other messed up my reports.

 

Saying this you need to consider two important informations into account when making the change of datasources is that:

  • Do not chan get the query name that is serving as based of your data
  • Always keep the number of columns and names on the first update of the data

This two rules means that if you have a query called Query_1 with columns ID, Date, Amount you should make the transformation to keep these format, also be carefull with the dat type fo each column but that is easier to solve.

 

Secondly when making this type of change (from one type of datasource to another) I ussually make the new connection as if it was a new table then on the advance editor I copy the source code and other steps if needed and paste them on the previous query overwriting the source and other specific steps from that one.

 

On your case this would be replacing all steps from source to Changed type.

 

Regarding your question about the table source I'm assuming that when you refer to table is another query you just need to replace the source by the name of the other query (table) but once again since the outcome of that query is not the same as the excel file you need to delete the other 3 steps below and then reference the Group to the source.

 

Check below your code edited in the way I'm refering please change the name by the correct names:

 

let
Source = NEW_TABLE_REFERENCE,
#"Grouped Rows" = Table.Group(Source, {"actual_date_interval", "channel", "Project"}, {{"units_completed", each List.Sum([units_completed]), type number}, {"total_assignments", each List.Sum([total_assignments]), type number}, {"hours_worked", each List.Sum([hours_worked]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"actual_date_interval", Order.Descending}})
in
#"Sorted Rows"

Be aware that if all the fields in the NEW_TABLE_REFERENCE don't match the ChangedType step of the previous query you will get erroros so in this case you need to have at least the fields below on your NEWTABLE:

 

  • "actual_date_interval"
  • "channel"
  • "Project"
  • "total_assignments"
  • "hours_worked"

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

Right, so the the easiest way is to create a new PBIX. Connect to the Excel table as a data source. Go into Advanced Editor. Copy the first two lines and replace the two lines in your current PBIX Advanced Editor. Make sure the step names match. Is that what you are asking?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

I am good now on the solution provided. Still I appreciate the response and thank you! 🙂 

MFelix
Super User
Super User

Hi @krixtsup3r ,

 

In my work with PBI I have add several times to change the datasource and in my experience there are some things that went good and other messed up my reports.

 

Saying this you need to consider two important informations into account when making the change of datasources is that:

  • Do not chan get the query name that is serving as based of your data
  • Always keep the number of columns and names on the first update of the data

This two rules means that if you have a query called Query_1 with columns ID, Date, Amount you should make the transformation to keep these format, also be carefull with the dat type fo each column but that is easier to solve.

 

Secondly when making this type of change (from one type of datasource to another) I ussually make the new connection as if it was a new table then on the advance editor I copy the source code and other steps if needed and paste them on the previous query overwriting the source and other specific steps from that one.

 

On your case this would be replacing all steps from source to Changed type.

 

Regarding your question about the table source I'm assuming that when you refer to table is another query you just need to replace the source by the name of the other query (table) but once again since the outcome of that query is not the same as the excel file you need to delete the other 3 steps below and then reference the Group to the source.

 

Check below your code edited in the way I'm refering please change the name by the correct names:

 

let
Source = NEW_TABLE_REFERENCE,
#"Grouped Rows" = Table.Group(Source, {"actual_date_interval", "channel", "Project"}, {{"units_completed", each List.Sum([units_completed]), type number}, {"total_assignments", each List.Sum([total_assignments]), type number}, {"hours_worked", each List.Sum([hours_worked]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"actual_date_interval", Order.Descending}})
in
#"Sorted Rows"

Be aware that if all the fields in the NEW_TABLE_REFERENCE don't match the ChangedType step of the previous query you will get erroros so in this case you need to have at least the fields below on your NEWTABLE:

 

  • "actual_date_interval"
  • "channel"
  • "Project"
  • "total_assignments"
  • "hours_worked"

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



I super appreciate your help on this!

 

This is what I'm looking for and I wouldn't have problem I think since I am not expecting changes in my sources. Though I have tons of grouped table that I need to edit one by one. 

 

Thank you so much for this!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.