Transforming a column to obtain 2 ones of different values

[bebo87]

Hi community, 

have this issue that I would like you to check. So, I have a series of data in a column as follows. This list is actually the upload of a barcode scanner where the text information is a group of product and the numerical value is the product (always intended as 1 unit). 

FB_608
99001834
99001834
VIS237
99001872
99001872
WP58/10X
99001889
99001872
VIS237
99002039
P9X_23
99002039
WP58/10_X03
99001773

What I am seeking your help for is to transform this series of values into this:

FB_608	99001834
FB_608	99001834
VIS237	99001872
VIS237	99001872
WP58/10X	99001889
WP58/10X	99001872
VIS237	99002039
P9X_23	99002039
WP58/10_X03	99001773

 I tried some ideas but I couldn't make it

[LQuedas]

Hey  @bebo87 ,

the solution @Greg_Deckler  provided works well to me...

Maybe you need to review your M-Query code, maybe you have a typo or so...

Cheers, LQ

[Greg_Deckler]

@bebo87 Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnOKNzOwUIrViVaytDQwMLQwNsHkhHkGGxmbI4mbG2FywgNMLfQNDSKQZCwsMZWhmWVkYAxRFGAZEW9kjCkONTY+wgAhaWhuDuTEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Column2", each if Text.StartsWith([Column1],"9") then null else [Column1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Column3", each if Text.StartsWith([Column1],"9") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Column2"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column3] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"})
in
    #"Removed Columns"

[bebo87]

hi @Greg_Deckler 

i get this syntax error: 

let
    Source = Excel.CurrentWorkbook(){[Name="Tabella3"]}[Content], let _
t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Column2", each if Text.StartsWith([Column1],"9") then null else [Column1]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Column3", each if Text.StartsWith([Column1],"9") then [Column1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"Column2"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Column3] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1"})
in
    #"Removed Columns"

toke identifier expected

[Greg_Deckler · Accepted solution]

@bebo87 Does it point you to a line number or anything? Everything looks identitical other than the Source step. Does the query work if you just paste the original code into a Blank Query? Should.

[bebo87]

It works, the problem comes when I try to change the data source which in my case is a range in excel where in this part 

let _
t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),

doesn't go ahead. 

Th solution brought by the other user @LQuedas  is not working either as powerquery is making errors where records do not start with 9.

May I kindly ask you just to rephrase the code string with a source coming from a table/Range. It's quite basic: random spreadsheet, sheet 1, column1

I believe we should fix it this way