0

I need to create a custom column which looks at 1 column, a, and based on a, trims a value from b and puts the value in c.

Part B: Now if the value in a does not match my criteria, then it looks at column d to find if it was the same as the previous row and if yes then puts the value of c from the previous row into the new row.

I have one part of it now:

if Text.End ([SAP segment name],3) = "K09"
then Text.Trim (Text.Start([Application data],35)) else ""

I need help with Part B

BenT
  • 3,172
  • 3
  • 18
  • 38
Rp2019
  • 1
  • 2

1 Answers1

0

Your formula [B3=IF(RIGHT(R3,3)="K09",TRIM(LEFT(W3,35)), B2) ] will return an error for every row until the first time R ends in K09. However if that's what you want then 

let Source = Table.FromList({
[SAP segment name="L04K29", Application data ="ABCDEFGHIJKLMNOP12345VWXYZabcdefghijklmnopqrstuvwxyz"] ,  
[SAP segment name="R03L09", Application data ="abcdefghijklmnop67890qrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"] ,  
[SAP segment name="L04K09", Application data ="ABCDEFGHIJKLMNOP987654QRSTUVWXYZabcdefghijklmnopqrstuvwxyz"] ,  
[SAP segment name="L04K09", Application data ="ABCDEFGHIJKLMNOPQR321254STUVWXYZabcdefghijklmnopqrstuvwxyz"] ,  
[SAP segment name="R03G09", Application data ="abcdefghijklmnopqrst87562uvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"] ,  
[SAP segment name="L04K09", Application data ="ABCDEFGHIJKLMNOPQRSTU45265VWXYZabcdefghijklmnopqrstuvwxyz"] 
}, Record.FieldValues, {"SAP segment name", "Application data"}),
#"Added Custom" = Table.AddColumn(Source, "Custom", each if Text.End ([SAP segment name],3) = "K09" then Text.Trim(Text.Start([Application data],35)) else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
in
#"Filled Down"
horseyride
  • 17,007
  • 2
  • 11
  • 22
  • I am getting errors:Expression.Error: The name 'PreviousStep' wasn't recognized. Make sure it's spelled correctly. – Rp2019 Jul 03 '19 at 18:12
  • because you were supposed to use the name of your previous step, not literally use the text "PreviousStep" – horseyride Jul 03 '19 at 18:50
  • I am trying to convert excel this formula into a power query formula: B3=IF(RIGHT(R3,3)="K09",TRIM(LEFT(W3,35)), B2) – Rp2019 Jul 03 '19 at 19:21
  • I did understand the error and correct it, but it doesn't do what I wanted it to – Rp2019 Jul 03 '19 at 19:23
  • changed answer above – horseyride Jul 05 '19 at 18:12
  • This is what I have right now. =if [#"Segment No."] = 1 then Text.Trim (Text.Start([Application data],35)) else {[Index]}{[PO Number]}............................................. but It is having trouble getting the previous row's value for PO Number – Rp2019 Jul 09 '19 at 12:27