Forum Discussion
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 th...
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 Resultfreginier 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 - 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))
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
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 Result2. 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