Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

duplicate or parse a column with different length characters

I have a column with an assortment of the following example data.   null R266A0744 S2370B1030 S4273A0940   This data represents (Model R,S2,S4)(Julian date 3 char)(Machine 1 char)(Time 4 char...
Need Help
  • edhans's avatar
    edhans
    4 years ago

    You would need to write something along the lines of this:

    if Text.Length([data column]) = 9 then Text.Start([data column],1) else Text.Start([data column]2)

    Then do the same fo rthe other 3 parts, but using Text.Middle([data column],2,3) or whatever.

    Just remember in Power Query, it starts counting at 0, so to get the 2nd and 3rd char, it would be Text.Middle([data column], 1, 2) (2nd column, 2 chars)

Thingsclump's avatar
Thingsclump
Resolver V
4 years ago

Hi Anonymous 

 

Here you go. 

 

Input

 

Output

 

 

Code:

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjIyM3M0MDcxUYrViVYKNjI2N3AyNDA2gHBNjMyNHQ0sTYDcWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Inserted Text Length" = Table.AddColumn(#"Changed Type", "Length", each Text.Length([Column1]), Int64.Type),
    #"Duplicated Column" = Table.DuplicateColumn(#"Inserted Text Length", "Column1", "Column1 - Copy"),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column1 - Copy.1", "Column1 - Copy.2"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Column1 - Copy.2", Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"}), {"Column1 - Copy.2.1", "Column1 - Copy.2.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Character Transition1",{{"Column1 - Copy.2.2", "Time"}, {"Column1 - Copy.2.1", "Machine"}, {"Column1 - Copy.1", "Model,date"}}),
    #"Added Conditional Column" = Table.AddColumn(#"Renamed Columns", "Custom", each if [Length] = 9 then Text.Start([#"Model,date"], 1) else Text.Start([#"Model,date"], 2)),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each Text.End([#"Model,date"], 3)),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "Model"}, {"Custom.1", "date"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Model,date", "Length"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1", "Model", "Machine", "Time", "date"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Reordered Columns",{{"Machine", type text}, {"Time", type text}, {"date", type text}, {"Model", type text}}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Changed Type1",{"Column1", "Model", "date", "Machine", "Time"})
in
    #"Reordered Columns1"

 

 

Please accept it as solution if it solves your issue.

 

Thanks

www.thingsclump.com