Need help with power query

[Samhunt]

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. 

[Samhunt]

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 reference	Check-out date	Extras amount (€)	Commercial margin (€)
19589205	07/10/2023	60	0
20161555	12/10/2023	60	0
18420055	22/10/2023	55	0
19436218	28/10/2023	60	0
20321052	05/11/2023	60	0
Subtotal		295	0
Napa Living 20
Booking reference	Check-out date	Extras amount (€)	Commercial margin (€)
20069136	10/10/2023	28	0
20163033	19/10/2023	28	0
20252619	24/10/2023	28	0
20282267	01/11/2023	28	0
Subtotal		112	0

and this is what I want after: 

	Sales profit margin per accommodation and booking
0
20 ATR -Trinity (1) Apartment	20 ATR -Trinity (1) Apartment
20 ATR -Trinity (1) Apartment	Booking reference	Check-out date	Extras amount (€)	Commercial margin (€)
20 ATR -Trinity (1) Apartment	19589205	07/10/2023	60	0
20 ATR -Trinity (1) Apartment	20161555	12/10/2023	60	0
20 ATR -Trinity (1) Apartment	18420055	22/10/2023	55	0
20 ATR -Trinity (1) Apartment	19436218	28/10/2023	60	0
20 ATR -Trinity (1) Apartment	20321052	05/11/2023	60	0
20 ATR -Trinity (1) Apartment	Subtotal		295	0
20 ATR -Trinity (1) Apartment
Napa Living 20	Napa Living 20
Napa Living 20	Booking reference	Check-out date	Extras amount (€)	Commercial margin (€)
Napa Living 20	20069136	10/10/2023	28	0
Napa Living 20	20163033	19/10/2023	28	0
Napa Living 20	20252619	24/10/2023	28	0
Napa Living 20	20282267	01/11/2023	28	0
Napa Living 20	Subtotal		112	0
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

[v-xinruzhu-msft]

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]

Hello @v-xinruzhu-msft

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_l..."),
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. 

[PijushRoy]

Hi @Samhunt 

Please go through this video & you will get idea how it should work and implement in your project
https://www.youtube.com/watch?v=Hc3d8rMSXcQ 

If solved your requirement, please mark this answer as SOLUTION.
If this comment helps you, appreciate your KUDOS 

Thanks
Pijush