Forum Discussion

Samhunt's avatar
Samhunt
Helper II
2 years ago

Need help with power query

Hi Guys I am new to power bi and I need some help. 

 

I will not post any tables as it gets my message as spam. 

 

This is the formula that I use in excel and I add it in Column A and I start from cell A2 IF=(B1="",B2,A1)

 

So what I want is if the cell in column B on the row above the cell with the formula is empty return the cell in the next column on the same row as my cell, if not return the value of the cell on the row above the cell where the formula is. 

 

I hope it helps understand. 

 

 

Need Help

4 Replies

  • Samhunt's avatar
    Samhunt
    Helper II
    2 years ago

    Hi @PijushRoy 

     

    Unfortunately, I did not understand much, I don't know if it's because I am very new to this or if this is the right video.  This is my initial table: 

    Sales profit margin per accommodation and booking   
        
    20 ATR -Trinity (1) Apartment   
    Booking referenceCheck-out dateExtras amount (€)Commercial margin (€)
    1958920507/10/2023600
    2016155512/10/2023600
    1842005522/10/2023550
    1943621828/10/2023600
    2032105205/11/2023600
    Subtotal 2950
        
    Napa Living 20   
    Booking referenceCheck-out dateExtras amount (€)Commercial margin (€)
    2006913610/10/2023280
    2016303319/10/2023280
    2025261924/10/2023280
    2028226701/11/2023280
    Subtotal 1120
        

     

    and this is what I want after: 

     Sales profit margin per accommodation and booking   
    0    
    20 ATR -Trinity (1) Apartment20 ATR -Trinity (1) Apartment   
    20 ATR -Trinity (1) ApartmentBooking referenceCheck-out dateExtras amount (€)Commercial margin (€)
    20 ATR -Trinity (1) Apartment1958920507/10/2023600
    20 ATR -Trinity (1) Apartment2016155512/10/2023600
    20 ATR -Trinity (1) Apartment1842005522/10/2023550
    20 ATR -Trinity (1) Apartment1943621828/10/2023600
    20 ATR -Trinity (1) Apartment2032105205/11/2023600
    20 ATR -Trinity (1) ApartmentSubtotal 2950
    20 ATR -Trinity (1) Apartment    
    Napa Living 20Napa Living 20   
    Napa Living 20Booking referenceCheck-out dateExtras amount (€)Commercial margin (€)
    Napa Living 202006913610/10/2023280
    Napa Living 202016303319/10/2023280
    Napa Living 202025261924/10/2023280
    Napa Living 202028226701/11/2023280
    Napa Living 20Subtotal 1120
    Napa Living 20    

     

    I think a formula would do the job. 

     

    So from what I understand my formula should look like this

    =if [Booking Reference][Index]-1 = "" then [Booking Reference] else 

     

    From what I understand this part "[Booking Reference][Index]-1" Targets the cell in the previous row of the Booking Reference column and if that is equal to "" it will return the cell of that column in the same row. 

     

    As I said the else part I want it to bring the value of the previous row in the column I am creating now. 

     

    Regarding the else part, I want it to return the previous cell value of the new column that will get created

     

    Maybe the fact that I am completely clueless about power query and I only know excel formulas does not help me comprehend how this works and I am thinking about something that is not feasible.

     

    Regards

    Samhunt

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Samhunt 

      You can put the following code to advanced editor in power query

      let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vZCxTsMwEEB/xcrUSq1yd47deCyIDTFAt6iDCaZYbezKOAhWvoev4kuI00ikUrNW8uJ3T7buVVVGwNabR7bcBOts/GIznLP1UYfYGBezRTac7aLKbrzfW7djwbyaYFxtusHtm6n3S99G9qJjAnefMeh3phvfushmv98/86T5pjGhtvrAGh121g2T9CwqUSoC0WmwyhFyAuLdRUIivUKAEoVICtJlBcuCAHqFxkpPBkUVXBKWSSmnPuKEICgBkSNeUJ7a5+ijPpzCkPp//0EfNbu3H6kRwVXadStLhVymMDBaicqzdhx4oqimFBIkUSVaTColkVwlgKMwI+U8DCKdJts/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Column2]="" and [Column3]="" and [Column4]="" then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",{"Custom", "Column1", "Column2", "Column3", "Column4"})
in
    #"Reordered Columns"

      Output

      Best Regards!

      Yolo Zhu

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Samhunt's avatar
        Samhunt
        Helper II
        2 years ago

        Hello Anonymous

         

        This works, but it will only return me the information that you posted, if the data set is bigger, it is a few thousand raws, it will still return me the first 15 raws. 

         

        This code was posted in the Advanced Editor, let
        Source = GoogleSheets.Contents("https://docs.google.com/spreadsheets/d/15H2YmBRAGo0fF-CEZkGNBD35KnRlwJscaHsZ0h_faEo/edit?usp=drive_link"),
        Sheet1_Table = Source{[name="Sheet1",ItemKind="Table"]}[Data],
        #"Removed Columns" = Table.RemoveColumns(Sheet1_Table,{"Column2", "Column3", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column5"})
        in
        #"Removed Columns" 

         

        I had to remove it and paste the code you shared with me. 

         

        What I totally don't understand is the following, I removed my data set and went in the initial source and changed it. I removed the top 15 rows which are the data that are used by the results I received. 

         

        Then I imported it again, so as you understand the data was different. 

         

        I copied the code you provided in advanced editor and the result was the same. Different data same result very strange.