Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I want to append one data set to another.
I have:
The issue is that the returns data set is in absolute values.
So, SKU 12345 sold 5 units, and there were 3 units returned - the net sales is 2 units.
In current form, if I simply append the returns data to sales data, it would incorrectly show as if we sold 8 units of SKU 12345.
How do I convert the returns data quantities and $ figures to negatives and append to the sales data sheet?
Reasoning for not using measures: Whilst I can use Measures to obtain the net sales, there are problems later on in displaying data for each SKU. For example, I am limited in the relationships I can create between sheets, as there are already relationships with a date table, etc. I am unable to create a relationship by SKU between sales and returns tables, thus they must be combined.
(Data sets are coming from a SQL server that refreshes daily. The way our DWH is set up requires that sales and returns sheets come in separate form - so it's not a data table in excel I can simply play around with, combine, and upload)
Thanks for all the help! This forum has been incredibly valuable.
Solved! Go to Solution.
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
@APM in case you are importing data into powerbi, you can use below methods
1) when you are merging two tables, you can create one more column to identify source data like Sales or Return. Using this column you can create a measure in powerbi which can give you effective sales like
Sales= sum(sales), Sales_Type="Sales"
Return = sum(sales), Sales_Type="Return"
Effective Sales = sales - return
2) second option is to add one more column in both table like final sale. In the sale table, this column will have the same value as the sales but in return table, you can have negative values instead of actual return. This way after merge you will be able to see the effective sales
i hope you are able to implement one of the above solution.
Proud to be a Super User!
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
107 | |
106 | |
90 | |
61 |
User | Count |
---|---|
165 | |
136 | |
134 | |
97 | |
86 |