'Split' a column or process query to create 2 columns

[Oros]

Hello. 

I have a column PACKAGING that has diffirent UNIT data (G, G*, GR, L, LT, KG, UN).  There are also some records where the values are enclosed in parenthesis ( ).

Here is the column that I would like to 'split' this column (EXISTING COLUMN) to create 2 columns (RESULTS COLUMNS).  Thanks.

[ChrisMendoza · Accepted solution]

@Oros - Maybe there's a better way...

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("PY1LCoAwDETv4kpFZJKSoifowupCFAKl97+Gqdru5uUzL6WOoE4QujxZZqWZJVZgQjg/8OqU2x2UgDBWcL7dQQXYW0O/KNty+Ni+tr9BlOL1RtOYs85L8X38WcyzFmd+AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Packaging = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"(","",Replacer.ReplaceText,{"Packaging"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value",")","",Replacer.ReplaceText,{"Packaging"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "Packaging", Splitter.SplitTextByEachDelimiter({"X"}, QuoteStyle.Csv, false), {"Packaging.1", "Packaging.2"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Packaging.2", Splitter.SplitTextByEachDelimiter({"X"}, QuoteStyle.Csv, false), {"Packaging.1.1", "Packaging.1.2"}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Split Column by Delimiter1", "Packaging.1.1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9", "."}, c)), {"Packaging.1.1.1", "Packaging.1.1.2"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Packaging.1.2", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Packaging.1.2.1", "Packaging.1.2.2"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Character Transition1",{"Packaging.1.1.2", "Packaging.1.2.2"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
    #"Changed Type" = Table.TransformColumnTypes(#"Merged Columns",{{"Packaging.1", type number}, {"Packaging.1.1.1", type number}, {"Packaging.1.2.1", type number}}),
    #"Replaced Value2" = Table.ReplaceValue(#"Changed Type",null,1,Replacer.ReplaceValue,{"Packaging.1.2.1"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value2", "Custom", each [Packaging.1] * [Packaging.1.1.1] * [Packaging.1.2.1]),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Packaging.1", "Packaging.1.1.1", "Packaging.1.2.1", "Custom", "Merged"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Reordered Columns",{"Custom", "Merged"})
in
    #"Removed Other Columns"

RETURNS

You can modify the function below to 'switch' abbreviations to full text values

fnSwitch( )

(input) =>
 
let
 
values = {
 
  {1, "Jul"},
  {2, "Aug"},
  {3, "Sep"},
  {4, "Oct"},
  {5, "Nov"},
  {6, "Dec"},
  {7, "Jan"},
  {8, "Feb"},
  {9, "Mar"},
  {10, "Apr"},
  {11, "May"},
  {12, "Jun"},
  {input, "Undefined"}
 
},
 
Result = List.First(List.Select(values, each _{0}=input)){1}
 
in
 
Result

@freginier had the right idea to start but you have to modify the built in GUI code to exclude the "."

Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9", "."}, c))

[Oros]

Thanks @ChrisMendoza 

[Oros]

Hi @ChrisMendoza ,

Thank you for your quick reply.

This is what I have in the advance query editor.  Do I just replace them all with the query that you wrote? Thanks again.

let
Source = Dynamics365BusinessCentral.ApiContents(null, null, null),
PRODUCTION = Source{[Name="PRODUCTION"]}[Data],
#"MAIN DATA" = PRODUCTION{[Name="MAIN DATA"]}[Data],
WebServices = #"MAIN DATA"{[Name="WebServices"]}[Data],
Item_Card_table = WebServices{[Name="Item_Card",Signature="table"]}[Data]

in
Item_Card_table

[v-yiruan-msft]

Hi @Oros ,

I create a sample pbix file, which is based on the response given by @ChrisMendoza with some minor changes, please check if it is the result you want. Please find the details the attachment.
1. Change the function fnSwitch( )

(input) =>
let
values = {
  {"G", "GRAMS"},
  {"GR", "GRAMS"},
  {"G*", "GRAMS"},
  {"L", "LITERS"},
  {"LT", "LITERS"},
  {"ML", "MLITERS"},
  {"KG", "KILOS"},
  {"UN", "UNIT"},
  {input, "Undefined"}
},
Result = List.First(List.Select(values, each _{0}=input)){1}
in
Result

2. Add a custom column to call the value inside the function

If you still not know how to achive it, please provide some sample data in Item_card_table. It is better if you can provide a simiplified pbix file with us. Thank you.

Best Regards

[freginier]

You can do this using split function in power query, try to use split with "Digit to non digit" you should have multiple column and you just have to do your calculation

[Oros]

HI @freginier ,

Thank you for the quick reply.  I did it but the 12X1.25L becomes 12 | 1 |  .25  |  L.  The .25 becomes separated.  Thanks again.