Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Csalinas144
Helper II
Helper II

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. 

@amitchandak 

@Greg_Deckler 

@parry2k 

@ryan_mayu 

@Ashish_Mathur 

@AllisonKennedy 

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 AData Set B

Data Set BData Set B

 

 

1 ACCEPTED 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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

17 REPLIES 17
watkinnc
Super User
Super User

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

 

 


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

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’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
watkinnc
Super User
Super User

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


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!
edhans
Super User
Super User

@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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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:

  1. Merge table A to Table B using the product name column.
  2. 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Expected ResultExpected Result

@edhans 

 

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 NameDescriptionPrice per Unit(US$)
3/10/2021Enjoylongboard$250.00 
5/10/2021Enjoylongboard$150.00
9/7/2021Enjoylongboard$100.00
3/10/2021Vulcomtrick board$55.00
3/10/2021MiniLogocruzer$125.00
3/4/2021ElementPro$300.00
5/4/2021ElementPro$200.00
9/4/2021ElementPro$95.00

 

Purchase Order Number

Product NameGenderAgeQuantityDateShip Date
1EnjoyM1513/5/20213/6/2021
2EnjoyM2217/12/20217/13/2021
3EnjoyF45110/10/202110/12/2021
4VulcomF1214/10/20214/12/2021
5MiniLogoF2215/5/20215/8/2021
6ElementM2314/20/20214/21/2021
7ElementM2416/15/20216/16/2021
8ElementF43210/25/202110/26/2021
9MiniLogoF2324/20/20214/21/2021
10EnjoyM15112/20/202112/22/2021
11VulcomM20312/21/202112/22/2021
12MiniLogoM20312/22/202112/23/2021
13VulcomM21312/23/202112/24/2021
14EnjoyM21112/24/202112/26/2021
15MiniLogoM21112/25/202112/26/2021
16ElementF21112/26/202112/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.

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Share Point Link to Data 

 

Here is a link to my data. Particulary the Product and Sales page. 

It is requiring a signin. Needs to be shared from your OneDrive account with anyone.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

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!!


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Greg_Deckler
Super User
Super User

@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])
  )

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Where do I need to fill in my inputs using your statement?

@Greg_Deckler 

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors