CODEWITHSUNDEEP
powerbipowerquery
Rasec Malkic
Rasec Malkic

Reputation: 659

Insert string based on content of current and previous row without helper columns?

I have this list for which I want to insert word "NEXT_COUNTRY" before each row containing "NAME" where previous rows is empty/blank.

WORLD
COUNT
COUNTRIES

NAME
CAPITAL

NAME
CAPITAL
POPULATION

COUNT
CAPITAL

Below is how it looks the input and output column I'm trying to get.

INPUT and OUTPUT

Is there a way to get this in a single step without creating several helper columns?

I'm trying with List.Accumulate() (taking example from here) like below but still don't get desired output.

= List.Accumulate(
     Source[DATA],  
     "",          
     ( state, current ) => 
          if state = "" then current else
          Text.Combine( { state } & { Text.From( current ) }, " - " )
)

UPDATE

Below is the current code but is not working.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUoCDcP8gHxelWB1kMWf/UL8QJDEwP8jTNRgshqZYwc/R1xVFzNkxwDPE0Yd0xWCBAP+AUB/HEE9/P6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INPUT = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"INPUT", type text}}),
    #"Trimmed Text" = Table.TransformColumns(ChangeType,{{"INPUT", Text.Trim, type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Trimmed Text", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each try if [INPUT] = null and Source[INPUT]{[Index]+1} = "NAME" then "NEXT_COUNTRY" else [INPUT] otherwise [INPUT])
in
    #"Added Custom"

Upvotes: 0

Views: 105

Answers (1)

horseyride
horseyride

Reputation: 21318

EDITED ANSWER

Try

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUoCDcP8gHxelWB1kMWf/UL8QJDEwP8jTNRgshqZYwc/R1xVFzNkxwDPE0Yd0xWCBAP+AUB/HEE9/P6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [INPUT = _t]),
ChangeType = Table.TransformColumnTypes(Source,{{"INPUT", type text}}),
#"Trimmed Text" = Table.TransformColumns(ChangeType,{{"INPUT", Text.Trim, type text}}),
#"Added Index" = Table.AddIndexColumn(#"Trimmed Text", "Index", 0, 1, Int64.Type),
#"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if [INPUT]="" and  #"Added Index"{[Index]+1}[INPUT]="NAME" then "NEXT_COUNTRY" else [INPUT])
in  #"Added Custom"

Upvotes: 1

Related Questions