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
Hi APM ,
In Power Query, select your values column, go to Transform tab, 'Standard', Multiply, then multiply by -1.
Once that's done, make sure the column headers are exactly the same in both tables (including data types and CASE).
Append.
Pete
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) If you select Append > Append as New you will retain both of the original tables and create a new combined one. If not, then you will just append the second table onto the bottom of your first one.
2) You need to make sure the columns have EXACTLY the same names and data types as one another. The best way to do this is to create a [Source] field in each of your original tables. One would be
= "Sales",
the other:
= "Returns".
Then rename your [Sales Unit] and [Return Unit] to just [Units], and the same for the $ fields.
3) As you are completing these transformations in Power Query, these changes will be applied every time you refresh your dataset. This depends on how frequently you set your refresh schedule. The SQL server will not "push" updates to your report as they happen, Power BI needs to "pull" updates at regular intervals.
Pete