Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSee 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello @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
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThank 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
@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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@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
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"
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
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
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!
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUh 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingRemoving 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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingSorry - 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 |
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.