cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
APM
Frequent Visitor

Convert table data and then append to another dataset

Hello,

 

I want to append one data set to another.

 

I have:

  • A sales data set with columns transaction date, item SKU, sales unit quantity, sales $ value, etc
  • A returns data set with columns transaction date, item SKU, return unit quantity, return $ value, etc.

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. 

3 ACCEPTED SOLUTIONS
BA_Pete
Super User II
Super User II

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

View solution in original post

APM
Frequent Visitor

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?

 

 

View solution in original post

BA_Pete
Super User II
Super User II

@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

View solution in original post

6 REPLIES 6
BA_Pete
Super User II
Super User II

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

View solution in original post

APM
Frequent Visitor

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?

 

 

View solution in original post

BA_Pete
Super User II
Super User II

@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

APM
Frequent Visitor

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?

BA_Pete
Super User II
Super User II

@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

View solution in original post

negi007
Super User II
Super User II

@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.

 

 




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


Proud to be a Super User!

Follow me here


Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors