Subtract value only if value in other colum is...

[rorand]

Hello.
I tried this in the "DAX Commands and tips"-forum, and was told it probably belongs here 🙂

I have a dataset with Sales Prices for different Items, Customers and start/ending dates.
Like this:

Item No	Customer	Starting Date	Unit Price	Ending Date
10000	A	01.01.2018	100	31.12.2018
10000	A	01.01.2019	110	31.12.2019
10000	A	01.01.2020	120
10000	B	01.01.2018	150	31.12.2018
10000	B	01.01.2019	160	31.12.2019
10000	B	01.01.2020	170
10000	Dicount A	01.01.2018	10	31.12.2018
10000	Dicount A	01.01.2019	11	31.12.2019
10000	Dicount A	01.01.2020	12
20000	A	01.01.2018	50	31.12.2018
20000	A	01.01.2019	60	31.12.2019
20000	A	01.01.2020	70
20000	B	01.01.2018	70	31.12.2018
20000	B	01.01.2019	75	31.12.2019
20000	B	01.01.2020	80
20000	Dicount A	01.01.2018	5	31.12.2018
20000	Dicount A	01.01.2019	6	31.12.2019
20000	Dicount A	01.01.2020	7

This is what I want:

IF Customer = A then subtract Discount A from A

IF Customer <> A then no subtraction

Expected result with filter Ending date = (blank):

Item No	Sales code	Unit Price
10000	A (minus disc)	108
10000	B	170
20000	A (minus disc)	63
20000	B	80

Thanks!

Regards

Andreas

[edhans]

See if this gets what you want. Paste this code in a Blank Query in Power Query using the Advanced Editor. (New Data Source, Blank Query, then hit Advanced Editor and clear out the defaults and put this in.)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fZFBC4MwDIX/Sul5iOnQ6nHOHXYZg7GTeBAnowcruPr/1+qQSRJFi0lfyMd7VSWvruvFbZAHeZ4+bui70f8+XDM6Y9+ibFzn66c1TtxH04biYl/rVX2oJMT+8f2T/2KI/KtiyHwBc/sIEailxarzoIaNOmfVKjRgPsVGVCCAZAegQADpDkCBADQGKE07TNYJwgmegxxaDOFxqKGfLyuV4nKhXCHFgYIyhRLP2/88UVwoemc7ykQn/HYUSYa3s4kkPAQbSMqzsHnoBan+Ag==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Item No", Int64.Type}, {"Customer", type text}, {"Starting Date", type date}, {"Unit Price", Int64.Type}, {"Ending Date", type text}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[Customer]), "Customer", "Unit Price", List.Sum),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {"Item No", "Starting Date", "Ending Date", "Dicount A"}, "Customer", "Unit Price"),
    #"Added Final Unit Price" = Table.AddColumn(#"Unpivoted Columns", "Final Unit Price", each if [Customer] = "A" then [Unit Price] - [Dicount A] else [Unit Price], Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Final Unit Price",{"Item No", "Starting Date", "Customer", "Final Unit Price"})
in
    #"Removed Other Columns"

You can filter that by date to just show the most recent year. You didn't have all of the years in your expected output.

[rorand]

Hello @edhans 

Thank you for your reply.

I have a few more colums than my example-data, and also I am connected to a SQL-server, so I am having a bit of trouble getting this converted to my original dataset.

Could you help me rewrite my Advanced Editor settings?

This is how it is shown now:

let
    Source = Sql.Database("SQLNAV", "LIVE"),
    #"dbo_LIVE$PBI Sales Price" = Source{[Schema="dbo",Item="LIVE$PBI Sales Price"]}[Data],
    #"Sorted Rows" = Table.Sort(#"dbo_LIVE$PBI Sales Price",{{"Item No_", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Ending Date", type date}, {"Starting Date", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",#date(1753, 1, 1),null,Replacer.ReplaceValue,{"Ending Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Sales Type] = 1))
in
    #"Filtered Rows"

Do you need anything more?

Regards

Andreas

[edhans]

You can take your code and my code and mash up this way:

let
    Source = Sql.Database("SQLNAV", "LIVE"),
    #"dbo_LIVE$PBI Sales Price" = Source{[Schema="dbo",Item="LIVE$PBI Sales Price"]}[Data],
    #"Sorted Rows" = Table.Sort(#"dbo_LIVE$PBI Sales Price",{{"Item No_", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Ending Date", type date}, {"Starting Date", type date}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type",#date(1753, 1, 1),null,Replacer.ReplaceValue,{"Ending Date"}),
    #"Filtered Rows" = Table.SelectRows(#"Replaced Value", each ([Sales Type] = 1)),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Changed Type"[Customer]), "Customer", "Unit Price", List.Sum),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {"Item No", "Starting Date", "Ending Date", "Dicount A"}, "Customer", "Unit Price"),
    #"Added Final Unit Price" = Table.AddColumn(#"Unpivoted Columns", "Final Unit Price", each if [Customer] = "A" then [Unit Price] - [Dicount A] else [Unit Price], Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Final Unit Price",{"Item No", "Starting Date", "Customer", "Final Unit Price"})
in
    #"Removed Other Columns"

My code picks up a the #"Pivoted Columns" step.

But it assumes it is the column names and types you had in your sample data. If that doesn't work, then I need to more info. Your sample data must include all columns that are relevant to the data being transformed. 

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[rorand]

Thank you so much for your time and effort @edhans !

I tried to mend your code to fit my columns and data, but dit not quite get there 😅

These are my original columns:

Please be aware of the following:

"Sales Code" = "Customer" in my example data

The "Sales Codes" that are to be subtracted are:

"G-NG" = "A" in my example data

"NG RABATT" = "Discount A" in my example data

This is how your code ended up with my mending of it (only from Pivoted Column), where did I go wrong?

#"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Changed Type"[Sales Code]), "Sales Code", "Unit Price", List.Sum),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {"Item No_", "Sales Type", "Starting Date", "Unit of Measure Code", "Ending Date", "NG Rabatt"}, "Customer", "Unit Price"),
    #"Added Final Unit Price" = Table.AddColumn(#"Unpivoted Columns", "Final Unit Price", each if [Sales Code] = "G-NG" then [Unit Price] - [NG Rabatt] else [Unit Price], Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Final Unit Price",{"Item No_","Sales Type", "Starting Date", "Unit of Measure Code", "Ending Date", "Sales Code", "Final Unit Price"})
in
    #"Removed Other Columns"

The error-message is:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Number=207
Class=16

Regards

Andreas

[edhans]

@rorand can you paste the actual sample data (fake data good, correct column names a must!)? I'm having trouble reading my own M code and often walk through it in Power QUery to make sure I understand what it is doing. I don't do List.Sum() in my head. 😁

I just need 5-10 rows of data. The 2nd link below is the most relevant here to getting it in the forum correctly.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[rorand]

@edhans - no problem 🙂

As I mentioned:
If "Sales Code" = "G-NG" then subtract "G-NG" with "NG RABATT"

Both "G-NG" and "NG RABATT" are values in the "Sales Codes" column.

Item No_	Sales Type	Sales Code	Starting Date	Unit of Measure Code	Unit Price	Ending Date
10106	1	G-NG	01.07.2018	BR	22,1	31.12.2018
10106	1	G-NG	01.01.2019	BR	22,17	31.01.2019
10106	1	G-NG	01.02.2019	BR	22,63	18.03.2019
10106	1	G-NG	19.03.2019	BR	22,63	30.06.2019
10106	1	G-NG	01.07.2019	BR	23,08	31.12.2019
10106	1	G-NG	01.01.2020	BR	23,23	null
10106	1	G-RE	02.07.2018	BR	23,13	31.12.2018
10106	1	G-RE	01.01.2019	BR	20,9	06.03.2019
10106	1	G-RE	07.03.2019	BR	20,9	30.06.2019
10106	1	G-RE	01.07.2019	BR	21,52	31.12.2019
10106	1	G-RE	01.01.2020	BR	21,72	23.01.2020
10106	1	G-RE	24.01.2020	BR	22,15	null
10106	1	NG RABATT	01.01.2020	BR	44,72	null

Regards

Andreas

[edhans · Accepted solution]

See if this works. THe final step is for you to remove the unneeded columns.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZI9DoMwDEavUjG7kX+AwAhSxcaA2BA3QN16/8aNigIhYSAksp+++CnLUhAS1gUU5L7hOQ7uh2TQGkZq3KGf3MIMWhcyxL6wQhIl7WhD1Hr2X8mwfGZr0abGoGRZaveOEytosL7NtUdWAJtw3iyrUzEGLGvu+7NtF9T0UoojwQIkd4Y9GxtG0L2bMi3JozaS5NGsoz315Iig4jtHhxsHjggs/6beKwmWy4h176lK+R2Hx9T13Txfxpalj/Xo+gU=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Item No_" = _t, #"Sales Type" = _t, #"Sales Code" = _t, #"Starting Date" = _t, #"Unit of Measure Code" = _t, #"Unit Price" = _t, #"Ending Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Item No_", Int64.Type}, {"Sales Type", Int64.Type}, {"Sales Code", type text}, {"Starting Date", type text}, {"Unit of Measure Code", type text}, {"Unit Price", Int64.Type}, {"Ending Date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"Starting Date", type date},{"Ending Date", type date}}, "en-AU"),
    #"Pivoted Column" = Table.Pivot(#"Changed Type with Locale", List.Distinct(#"Changed Type with Locale"[#"Sales Code"]), "Sales Code", "Unit Price", List.Sum),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {"Item No_", "Sales Type", "Starting Date", "Unit of Measure Code", "Ending Date", "NG RABATT"}, "Customer", "Unit Price"),
    #"Replaced Value" = Table.ReplaceValue(#"Unpivoted Other Columns",null,0,Replacer.ReplaceValue,{"NG RABATT"}),
    #"Added Final Unit Price" = Table.AddColumn(#"Replaced Value", "Final Unit Price", each if [Customer] = "G-NG" then [Unit Price] - [NG RABATT] else [Unit Price], Int64.Type)
in
    #"Added Final Unit Price"

[rorand]

@edhans 

Wow, my first answer did only get your name, here is what it said:

I got it to work!

Had a bit of trouble with a #Changed Type step I hade before the #Pivoted Column, and also I did not get it to work with your #Changed Type and #Change Type With Locale steps (even if I fixed them to my previous steps), but as soon as I removed all of the #Changed Type steps from before #Pivoted Colum, it worked like a charm!

Thank you very much, this is genious 🙂

Regards Andreas

[edhans]

yes @rorand - Excellent point and my bad for not calling that out for you. I am in the US so my date format is MM/DD/YYYY and when I recognize a DD/MM/YYYY I randomly change the data type to some other English format (I think I used AU this time) so I could get dates. Of course, that may totally bomb, and the fix is to just whack that step as your local install config in Windows is set to handle that data correctly, and Power BI honors that.

I'll be sure to inform people that I do that in PQ for it to work for me so they can potentially remove it.

Glad you got this to work and can keep your project moving!

[rorand]

Uh oh, I found a new problem @edhans 😅

The new column "NG RABATT" does not get the value on all of the "G-NG" lines.

This happens because the "Starting Date" is not always the same on "G-NG" and "NG RABATT". I did'nt notice that myself until I was working on my Visual and did not get the values to be correct.

Is it possible to change the #"Pivoted Column" so that it does'nt consider the "Starting Date"?

The "NG RABATT" value should appear on all lines that matches "Item No_" and "Ending Date".

Regards

Andreas

[edhans]

You could remove Starting Date before the Pivot. The Pivot is the Sales Code column and using the Net Price as the values. It takes into account all other columns still in the table.

I'm not sure how it would work with the End Date. That has some nulls in it.

Just go to the line before the Pivot Column and remove the Start Date and then go to the Pivot Column and see if it did what you wanted. 

If not, you may need to do some additional transformations first to get consistent dates, but I'd have to understand a lot more about why it isn't working, what it should be doing, and is this a problem in the source data that needs to be fixed, or is this just how the data works in the real world but needs to be suppliemented with some addtional logic to get it to work for you.

[rorand]

Removing the "Starting Date" worked to solve the problem I descriped.

That gives me a challange with how I want to visualize and filter my data, but I'll see if I can create a workaround in the report.

I guess there is no way to get the "Starting Date" column back in a later step?

Regards

Andreas

[edhans]

I don't know. I tried putting the dates in a nested table, but the PIVOT step stops and says all nested tables have to be removed befor pivoting, so that went out the window.

If you have some sort of unique id (or combination of fields) you could re-merge the table at the end with a prior step to get the dates. 

[rorand]

@edhans 

Sorry - something got wrong in the Unit Price values in the previous post.

Item No_	Sales Type	Sales Code	Starting Date	Unit of Measure Code	Unit Price	Ending Date
10106	1	G-NG	01.07.2018	BR	2210,4	31.12.2018
10106	1	G-NG	01.01.2019	BR	2217,6	31.01.2019
10106	1	G-NG	01.02.2019	BR	2263,2	18.03.2019
10106	1	G-NG	19.03.2019	BR	2263,2	30.06.2019
10106	1	G-NG	01.07.2019	BR	2308,8	31.12.2019
10106	1	G-NG	01.01.2020	BR	2323,2	null
10106	1	G-RE	02.07.2018	BR	2313,6	31.12.2018
10106	1	G-RE	01.01.2019	BR	2090,4	06.03.2019
10106	1	G-RE	07.03.2019	BR	2090,4	30.06.2019
10106	1	G-RE	01.07.2019	BR	2152,8	31.12.2019
10106	1	G-RE	01.01.2020	BR	217,2	23.01.2020
10106	1	G-RE	24.01.2020	BR	2215,2	null
10106	1	NG RABATT	01.01.2020	BR	447,2	null