Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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"
Solved! Go to Solution.
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:
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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsRight, 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?
I am good now on the solution provided. Still I appreciate the response and thank you! 🙂
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:
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:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsI 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!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |