Forum Discussion

Oros's avatar
Oros
Post Prodigy
4 years ago
Solved

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

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.

 

 

 

 

 

 

 

 

Need Help
  • ChrisMendoza's avatar
    ChrisMendoza
    4 years ago

    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))

6 Replies

  • ChrisMendoza's avatar
    ChrisMendoza
    Resident Rockstar
    4 years ago

    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's avatar
      Oros
      Post Prodigy
      4 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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's avatar
    freginier
    Solution Sage
    4 years ago

    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's avatar
      Oros
      Post Prodigy
      4 years ago

      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.