Forum Discussion
Convert table data and then append to another dataset
Thanks BA_Pete ! This worked.
Then I select 'append' and 'append as new' to combine them, right?
The other issue is that the columns have different names (Sale units VS returned units). How do I append so that they stack on top of each other?
Also, when the SQL server data is automatically refreshed, will this new appended data set also be refreshed?
APM ,
1) To add the [Source] columns, go to Power Query, select your Sales table, select the Add Column tab, 'Custom Column', call the column Source and put = "Sales" into the expression box below. Do exactly the same on your Returns table, but put = "Returns" in the expression box.
2) The order of data flow for Power BI reporting is generally as follows:
- Operational data is generated by the business and stored in a server - SQL Server in your case.
- Power Query pulls data from the server at regular intervals and performs transformations, such as changing positive values to negative, adding columns such as [Source], and renaming columns.
- Power Query pushes the transformed data to your data model, where you create relationships between tables and create measures etc.
- Visualisations sit on top of the model and pull the data they require into each chart/table/slicer from the model as it stands.
As you can see, your Power Query transformations are downstream from your SQL Server, and you have no loop pushing any data back to SQL Server from this point (this is possible, but not within scope of your scenario). Therefore, SQL Server doesn't care what you do with the data once it's been pulled into Power Query. It never sees it again and whatever you do in Power Query certainly won't cause any errors on the server side.
In summary: no, there isn't a solution without renaming columns, but this solution is usage-by-design and isn't going to cause you any server-side issues.
Pete
Hey bap1 , this sounds like a good option. How do I add the source field in each table?
Only thing is - wouldn't renaming the columns cause errors when the SQL server data refreshes and doesn't find the column names it is seeking?
Is there any solution without renaming the columns?
APM ,
1) To add the [Source] columns, go to Power Query, select your Sales table, select the Add Column tab, 'Custom Column', call the column Source and put = "Sales" into the expression box below. Do exactly the same on your Returns table, but put = "Returns" in the expression box.
2) The order of data flow for Power BI reporting is generally as follows:
- Operational data is generated by the business and stored in a server - SQL Server in your case.
- Power Query pulls data from the server at regular intervals and performs transformations, such as changing positive values to negative, adding columns such as [Source], and renaming columns.
- Power Query pushes the transformed data to your data model, where you create relationships between tables and create measures etc.
- Visualisations sit on top of the model and pull the data they require into each chart/table/slicer from the model as it stands.
As you can see, your Power Query transformations are downstream from your SQL Server, and you have no loop pushing any data back to SQL Server from this point (this is possible, but not within scope of your scenario). Therefore, SQL Server doesn't care what you do with the data once it's been pulled into Power Query. It never sees it again and whatever you do in Power Query certainly won't cause any errors on the server side.
In summary: no, there isn't a solution without renaming columns, but this solution is usage-by-design and isn't going to cause you any server-side issues.
Pete