Forum Discussion
Adding 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 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 AData Set B
Data Set B
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.
17 Replies
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?
Did 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.
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.edhans Whoops, good call, I missed which forum this was in!!
I 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:- Merge table A to Table B using the product name column.
- Extract the latest price using the Table.Max() function based on the [Date] in a calculated column. Include the [Price per unit] piece, then get the value using {0}
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.
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
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
Ok, 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
