Forum Discussion
Need help with power query
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
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.
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.