Forum Discussion

Csalinas144's avatar
Csalinas144
Icon for Helper II rankHelper II
5 years ago
Solved

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 Se...
Need Help
Tutorial Requests
  • edhans's avatar
    edhans
    5 years ago

    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.

     

Csalinas144's avatar
Csalinas144
Icon for Helper II rankHelper II
5 years ago

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. 

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

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.

    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      5 years ago

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

  • Csalinas144's avatar
    Csalinas144
    Icon for Helper II rankHelper II
    5 years ago

    Expected 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
    • edhans's avatar
      edhans
      Icon for Community Champion rankCommunity Champion
      5 years ago

      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.