Forum Discussion
Csalinas144
Helper II
Helper IIAdding a Custom Column with condition.
I have two data sets (A and B). I want to Add a Custom Column with condition to Data Set B. Custom Column added to Data Set B. If input in Data Set A is after or equal to input in Data Se...
This is the code Csalinas144
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJLDoIwEAbgu7AmKTNtAQ+gK9m6IawMMRiFjS68vVNo6c8rodASvs6D1nVCSZqc++fwk2clg6y7ydDKKs54mubTtEnrhFeA2YNCEQchcx2JBnKRYUIMypRc3rgFR2Tk1e37ug9vrygEMoDMwrh9q67vrsNj8GpOz8Z6rCqjyV1yr/bd9p9QkZ4DMQRiiqjYIuNRrmgOJHPoXLlAYyNcJJ5qZwuNYGCnnaKCO86QsqMfOzZsjiULaCARdn2sy+2j/Zd0xHiZ4wYyQjgZpDfxCJhGZoCZ9SEkKM6ggkaS3ckSoT2CyzNyWbscXeFd8wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order Number" = _t, #"Product Name" = _t, Gender = _t, Age = _t, Quantity = _t, Date = _t, #"Ship Date" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Ship Date", type date}}), #"Merged Queries" = Table.NestedJoin( #"Changed Type", {"Product Name"}, Prices, {"Product Name"}, "Prices", JoinKind.LeftOuter ), GetPrice = Table.AddColumn( #"Merged Queries", "Price", each let varDate = [Date] in try Table.Max( Table.SelectRows( [Prices], each [Date] < varDate ), "Date" )[#"Price per Unit(US$)"] otherwise 0 ), #"Removed Columns" = Table.RemoveColumns(GetPrice,{"Prices"}) in #"Removed Columns"Note that the first record returned 0 becuase there was no price set for March 5, 2021. Prices didn't start until March 10.
Here is the PBIX though. It will be much clearer what I did since there are two tables involved.
Csalinas144Helper II
Helper IIExpected Result
I need the Pricing Column to return the correct value of the product based off when it was purchased and the current price of the product.
Date
| Product Name | Description | Price per Unit(US$) | |
| 3/10/2021 | Enjoy | longboard | $250.00 |
| 5/10/2021 | Enjoy | longboard | $150.00 |
| 9/7/2021 | Enjoy | longboard | $100.00 |
| 3/10/2021 | Vulcom | trick board | $55.00 |
| 3/10/2021 | MiniLogo | cruzer | $125.00 |
| 3/4/2021 | Element | Pro | $300.00 |
| 5/4/2021 | Element | Pro | $200.00 |
| 9/4/2021 | Element | Pro | $95.00 |
Purchase Order Number
| Product Name | Gender | Age | Quantity | Date | Ship Date | |
| 1 | Enjoy | M | 15 | 1 | 3/5/2021 | 3/6/2021 |
| 2 | Enjoy | M | 22 | 1 | 7/12/2021 | 7/13/2021 |
| 3 | Enjoy | F | 45 | 1 | 10/10/2021 | 10/12/2021 |
| 4 | Vulcom | F | 12 | 1 | 4/10/2021 | 4/12/2021 |
| 5 | MiniLogo | F | 22 | 1 | 5/5/2021 | 5/8/2021 |
| 6 | Element | M | 23 | 1 | 4/20/2021 | 4/21/2021 |
| 7 | Element | M | 24 | 1 | 6/15/2021 | 6/16/2021 |
| 8 | Element | F | 43 | 2 | 10/25/2021 | 10/26/2021 |
| 9 | MiniLogo | F | 23 | 2 | 4/20/2021 | 4/21/2021 |
| 10 | Enjoy | M | 15 | 1 | 12/20/2021 | 12/22/2021 |
| 11 | Vulcom | M | 20 | 3 | 12/21/2021 | 12/22/2021 |
| 12 | MiniLogo | M | 20 | 3 | 12/22/2021 | 12/23/2021 |
| 13 | Vulcom | M | 21 | 3 | 12/23/2021 | 12/24/2021 |
| 14 | Enjoy | M | 21 | 1 | 12/24/2021 | 12/26/2021 |
| 15 | MiniLogo | M | 21 | 1 | 12/25/2021 | 12/26/2021 |
| 16 | Element | F | 21 | 1 | 12/26/2021 | 12/27/2021 |
edhans
Community Champion
Community ChampionThis is the code Csalinas144
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddJLDoIwEAbgu7AmKTNtAQ+gK9m6IawMMRiFjS68vVNo6c8rodASvs6D1nVCSZqc++fwk2clg6y7ydDKKs54mubTtEnrhFeA2YNCEQchcx2JBnKRYUIMypRc3rgFR2Tk1e37ug9vrygEMoDMwrh9q67vrsNj8GpOz8Z6rCqjyV1yr/bd9p9QkZ4DMQRiiqjYIuNRrmgOJHPoXLlAYyNcJJ5qZwuNYGCnnaKCO86QsqMfOzZsjiULaCARdn2sy+2j/Zd0xHiZ4wYyQjgZpDfxCJhGZoCZ9SEkKM6ggkaS3ckSoT2CyzNyWbscXeFd8wc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Purchase Order Number" = _t, #"Product Name" = _t, Gender = _t, Age = _t, Quantity = _t, Date = _t, #"Ship Date" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Ship Date", type date}}),
#"Merged Queries" =
Table.NestedJoin(
#"Changed Type",
{"Product Name"},
Prices,
{"Product Name"},
"Prices",
JoinKind.LeftOuter
),
GetPrice =
Table.AddColumn(
#"Merged Queries",
"Price",
each
let
varDate = [Date]
in
try
Table.Max(
Table.SelectRows(
[Prices],
each [Date] < varDate
),
"Date"
)[#"Price per Unit(US$)"]
otherwise 0
),
#"Removed Columns" = Table.RemoveColumns(GetPrice,{"Prices"})
in
#"Removed Columns"Note that the first record returned 0 becuase there was no price set for March 5, 2021. Prices didn't start until March 10.
Here is the PBIX though. It will be much clearer what I did since there are two tables involved.