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.
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 Set B, then Return the value of the latest record in Data Set A. if Not then return the next earliest value of the record in data set A.
Thank You,
Christian
@
How do I write this code?
Will I be able to refer to other sheet in the Query Editor while writing this condition?
Data Set A
Data Set B
Solved! Go to Solution.
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingOk, I see what you are aiming for now.
Go to table 2 (the sales table). In the formula bar, type:
= Table.AddColumn(NameOfPriorStep, "Sale Price", each let prod = each [Product], dd = each [Date] in List.Max(Table.Last(Table.SelectRows(table 1, each [Product] = prod and [Date] <= dd)))[Price Per Unit (USD)])
--Nate
OK, then you could still group Table B by product, using the List.Max aggregation, also add an All Rows Aggregation. Name the step Grouped. Name those columns Max Date, and Details.
Now you can add a column:
= Table.AddColumn(Grouped, "TheRecords", each if [Date] >= [Max Date] then Table.Max(Table.FindText(TableA, each [Product]), {"Date"}) else Table.Max(Table.SkipLastN(Table.FindText(TableB, each [Product])), {"Date"}))
--Nate
I would group Table A by Product Name and aggregate using Max Date. Then you can Table.Join Table B (left) to Table A (right) on the Date Field. Now you can just do some row calcs after the join, so like:
Table.Group(Table A, {"Product Name"}, {use the GUI to get the syntax correct, I'm taking a walk},
Then Join Table B to A on Date.
Then Table.AddColumn(PriorStep, "NewDate", each if [Date.1] < [Date] then [Date.1] else [Date])
Piece of cake!
--Nate
@Csalinas144 it would help if you would provide data we can use, and a screenshot of the desired results. @Greg_Deckler solution may work in the DAX/Power BI side, but you posted this in Power Query and refer to queries.
You could merge table b into a, but I don't know what you are expecting the results to be, so very hard to come up with a viable solution.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI apologize I am new to this and I will improve in the future @Greg_Deckler @edhans
The result that I am wanting is to return the price of an item based off the latest pricing change.
I am a bit vexed on this to begin.
I originally wanted to do all this in the power query editor. We can Start with that.
@Csalinas144 - can you please post some data? You are expecting us to help you by typing in all of that stuff you posted above in a image.
1) Post data per these links. It will let us copy and paste it into a table in Power BI.
2) Provide a screenshot (mocked up in Excel is fine) of the expected results.
Now, I kind of get #2 above, so that isn't a huge deal, but if you had posted data as I requested 4 hrs ago you'd have an answer.
Without data, here is what you do:
Make sense?
Or, post some data. 😁
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting
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 |
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingIt is requiring a signin. Needs to be shared from your OneDrive account with anyone.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@edhans Whoops, good call, I missed which forum this was in!!
@Csalinas144 Maybe:
Column in B =
VAR __Product = [Product Name]
VAR __Date = [Date]
VAR __TableA = FILTER('TableA',[Product Name]=__Product && [Date]>=__Date)
RETURN
IF(
ISBLANK(__TableA),
MINX(FILTER('TableA',[Product Name]=__Product),[Date]),
MAXX(__TableA,[Date])
)
Where do I need to fill in my inputs using your statement?
In line 4
VAR __TableA = FILTER('TableA',[Product Name]=__Product && [Date]>=__Date)
What does __Product&&[Date]>=__Date) mean exaclty. Trying to decipher this code.
Can you write between each line what each line is saying in english?
Thank You,
Christian
@Greg_Deckler Is it even possible to refer to a different table within a Calculated Column entry for a different table? Seems to not be an option to refer to a column in my other data set when writing this code.
Thank You for your help again!
It depends @Csalinas144 - what do you mean by "calculated column?" You mean a true Calculated Column in DAX? Then yes, use RELATED() or RELATEDTABLE() to access other columns through relationships. If you mean a Custom Column in Power Query, then again, yes, and about 10 different ways to do so. Merging is the easiest, but without data, and using specific terms, hard to know.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingDid you see the URL in my post above on how to post a table of data? Or you could provide a PBIX file with embedded content and share via OneDrive or Dropbox. I'll repost it, but everything we've ever needed to solve a solution is provided in the below info.
How to get good help fast. Help us help you.
How To Ask A Technical Question If you Really Want An Answer
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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.