split column by varying length per row based off character count

[lowei]

I'm trying to use Dax to vary the length of the split columns based off the total character count. I counted the characters in another column called length and divided by 2 with the intention of inserting that into the formula, but I can't get it to work.

Package size

9090

10001000

100100

5000050000

Should be

90

1000

100

50000

= Table.SplitColumn(Table.TransformColumnTypes(#"Renamed Columns3", {"Package size", type number}, "en-US"), "Package size", Splitter.SplitTextByPositions({0, 'Length'}, false), {"Package size.1", "Package size.2"})

[lowei]

Please see the attached for a sample of the document with what I have done so far.

I'm having a hard time taking the suggestions and applying them because I'm just learning all this, thanks for the patience!

https://www.dropbox.com/s/k0poeaued2zeqzf/Sample%20of%20data%20cleanup.pbix?dl=0

[tarunsingla]

Unfortunately I could not see the data you are connecting to in your pbix file. Would be helpful if you share the excel file ('C:\sample set.xlsx') as well.

Anyways, try this:

Step 1. From the Query Editor window, click on Advanced Editor (under Home menu).

Step 2. In the advanced editor, Replace the following lines (bottom 3 lines of your code):

#"Renamed Columns3" = Table.RenameColumns(#"Integer-Divided Column",{{"Trim.1.2.1.2", "Manufact"}})
in
#"Renamed Columns3"

with the following lines:

#"Renamed Columns3" = Table.RenameColumns(#"Integer-Divided Column",{{"Trim.1.2.1.2", "Manufact"}}),
#"Inserted First Characters" = Table.AddColumn(#"Renamed Columns3", "First Characters", each Text.Start([Package size], [Length]))
in
#"Inserted First Characters"

If this does not work, please upload the 'sample set.xlsx' file as well, so that we can see the columns and data types of each column.

Regards,
Tarun

[lowei]

My apologies!

https://www.dropbox.com/s/cdlrtqfa5nu14yy/sample%20set.xlsx?dl=0

I did try your solution, but it came back with an expression error:

Expression.Error: We cannot convert the value 9090 to type Text.
Details:
Value=9090
Type=Type

[tarunsingla · Accepted solution]

Thanks for sharing the excel file. The following solution should work:

Replace the following lines (bottom 3 lines):

#"Renamed Columns3" = Table.RenameColumns(#"Integer-Divided Column",{{"Trim.1.2.1.2", "Manufact"}})
in
#"Renamed Columns3"

with the following lines:

#"Renamed Columns3" = Table.RenameColumns(#"Integer-Divided Column",{{"Trim.1.2.1.2", "Manufact"}}),
#"Inserted First Characters" = Table.AddColumn(#"Renamed Columns3", "First Characters", each Text.Start(Text.From([Package size], "en-US"), Number.RoundUp([Length]))),
#"Inserted Last Characters" = Table.AddColumn(#"Inserted First Characters", "Last Characters", each Text.End(Text.From([Package size], "en-US"), Number.RoundUp([Length])))
in
#"Inserted Last Characters"

Regards,
Tarun

Did I answer your question? Mark my post as a solution!

[AlB]

@lowei 

Here's you initial code with the added steps in red at the end. I haven't checked your part, just added the steps you were asking for. Note that there's one value in the column 'Package size' with a pattern different from the others: 54,6. You'll need to fix that (if so required) in your steps. If you don't want the 'Package size 2' column at the end, just remove it.   

let
    Source = Excel.Workbook(File.Contents("C:\sample set.xlsx"), null, true),
    #"Filtered Hidden Files1" = Table.SelectRows(Source, each [Attributes]?[Hidden]? <> true),
    #"Expanded Data" = Table.ExpandTableColumn(#"Filtered Hidden Files1", "Data", {"Column1"}, {"Data.Column1"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Expanded Data", {"Name", "Source.Name"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"Source.Name", "Data.Column1"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Removed Other Columns",{{"Data.Column1", "Column1"}}),
    #"Split Column by Position" = Table.SplitColumn(#"Renamed Columns4", "Column1", Splitter.SplitTextByPositions({0, 11}, false), {"Column1.1", "Column1.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Column1.1", type number}, {"Column1.2", type text}}),
    #"Split Column by Position1" = Table.SplitColumn(#"Changed Type1", "Column1.2", Splitter.SplitTextByPositions({0, 30}, false), {"Column1.2.1", "Column1.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position1",{{"Column1.2.1", type text}, {"Column1.2.2", type text}}),
    #"Split Column by Position2" = Table.SplitColumn(#"Changed Type2", "Column1.2.2", Splitter.SplitTextByPositions({0, 21}, false), {"Column1.2.2.1", "Column1.2.2.2"}),
    #"Inserted Trimmed Text" = Table.AddColumn(#"Split Column by Position2", "Trim", each Text.Trim([Column1.2.2.2]), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Trimmed Text",{{"Column1.2.2.2", "Original"}, {"Column1.2.2.1", "GPI"}, {"Column1.2.1", "Description"}, {"Column1.1", "NDC"}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Renamed Columns", "Trim", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Trim.1", "Trim.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"GPI", type text}, {"Trim.1", type number}, {"Trim.2", type text}, {"Original", type text}}),
    #"Inserted Trimmed Text1" = Table.AddColumn(#"Changed Type3", "Trim", each Text.Trim([Trim.2]), type text),
    #"Reordered Columns1" = Table.ReorderColumns(#"Inserted Trimmed Text1",{"Source.Name", "NDC", "Description", "GPI", "Trim.1", "Trim", "Trim.2", "Original"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Reordered Columns1", "Trim", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Trim.1.1", "Trim.2.1"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Trim.1.1", type number}, {"Trim.2.1", type text}}),
    #"Inserted Trimmed Text2" = Table.AddColumn(#"Changed Type4", "Trim", each Text.Trim([Trim.2.1]), type text),
    #"Reordered Columns2" = Table.ReorderColumns(#"Inserted Trimmed Text2",{"Source.Name", "NDC", "Description", "GPI", "Trim.1", "Trim.1.1", "Trim", "Trim.2.1", "Trim.2", "Original"}),
    #"Removed Columns" = Table.RemoveColumns(#"Reordered Columns2",{"Trim.2.1", "Trim.2", "Original"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Removed Columns", "Trim", Splitter.SplitTextByEachDelimiter({"*"}, QuoteStyle.Csv, false), {"Trim.1.2", "Trim.2"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Trim.1.2", type text}, {"Trim.2", type text}}),
    #"Inserted Trimmed Text3" = Table.AddColumn(#"Changed Type5", "Trim", each Text.Trim([Trim.1.2]), type text),
    #"Reordered Columns3" = Table.ReorderColumns(#"Inserted Trimmed Text3",{"Source.Name", "NDC", "Description", "GPI", "Trim.1", "Trim.1.1", "Trim", "Trim.1.2", "Trim.2"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns3",{"Trim.1.2"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Removed Columns1", "Trim", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"Trim.1.2", "Trim.2.1"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Trim.1.2", type text}, {"Trim.2.1", type text}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type6",{{"Trim.1", "Package size"}, {"Trim.1.1", "Cost by unit"}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Renamed Columns2", "Trim.1.2", Splitter.SplitTextByEachDelimiter({"   "}, QuoteStyle.Csv, false), {"Trim.1.2.1", "Trim.1.2.2"}),
    #"Extracted Text After Delimiter" = Table.TransformColumns(#"Split Column by Delimiter4", {{"Trim.1.2.1", each Text.Combine(List.RemoveItems(Text.ToList(_),{"0".."9","."}))}}),
    #"Cleaned Text" = Table.TransformColumns(#"Extracted Text After Delimiter",{{"Trim.1.2.1", Text.Clean, type text}}),
    #"Split Column by Position3" = Table.SplitColumn(#"Cleaned Text", "Trim.1.2.1", Splitter.SplitTextByPositions({0, 1}, false), {"Trim.1.2.1.1", "Trim.1.2.1.2"}),
    #"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Position3",{{"Trim.1.2.1.1", type text}, {"Trim.1.2.1.2", type text}, {"Trim.1.2.2", type text}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Changed Type7",{"Trim.1.2.1.1"}),
    #"Inserted Text Length" = Table.AddColumn(#"Removed Columns2", "Length", each Text.Length(Text.From([Package size], "en-US")), Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Inserted Text Length", {{"Length", each Number.IntegerDivide(_, 2), Int64.Type}}),
    #"Renamed Columns3" = Table.RenameColumns(#"Integer-Divided Column",{{"Trim.1.2.1.2", "Manufact"}}),

    NewStepsFromHere = #"Renamed Columns3",  
    #"Changed Type" = Table.TransformColumnTypes(NewStepsFromHere,{{"Package size", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Middle([Package size],0,Text.Length([Package size])/2)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Middle([Package size],(Text.Length([Package size])/2), Text.Length([Package size]))),
    #"Renamed Columns1b" = Table.RenameColumns(#"Added Custom1",{{"Custom", "Package size 1"}, {"Custom.1", "Package size 2"}}),
    #"Removed Columnsb" = Table.RemoveColumns(#"Renamed Columns1b",{"Package size"}),
    #"Renamed Columns1c" = Table.RenameColumns(#"Removed Columnsb",{{"Package size 1", "Package size"}})
in
    #"Renamed Columns1c"

[tarunsingla]

Try this: Instead of split, use Extract First Characters command.

let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUorViVayNLA0ADPAhKGBgQEIo4gYIvNNgbIGYEIpNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Package size" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Package size", type number}}), #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Package size] <> null and [Package size] <> ""), #"Changed Type1" = Table.TransformColumnTypes(#"Filtered Rows",{{"Package size", type text}}), #"Inserted Text Length" = Table.AddColumn(#"Changed Type1", "Length", each Text.Length(Text.From([Package size], "en-US")), Int64.Type), #"Divided Column" = Table.TransformColumns(#"Inserted Text Length", {{"Length", each _ / 2, type number}}), #"Inserted First Characters" = Table.AddColumn(#"Divided Column", "First Characters", each Text.Start([Package size], [Length])) in #"Inserted First Characters"

Regards,

Tarun

[AlB]

Hi @lowei

I'm not sure whether you want one column in the end or two.

If you want two try this. Set Source = Your source table

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjSwNFCK1YlWMjQwMABhGMcQyjQFihmACaXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Package size" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Package size", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Middle([Package size],0,Text.Length([Package size])/2)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Middle([Package size],(Text.Length([Package size])/2), Text.Length([Package size]))),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "Package size 1"}, {"Custom.1", "Package size 2"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Package size"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Package size 1", "Package size"}})
in
    #"Renamed Columns1"

If you want one only, it's almost the same just with a  minor modification in the last lines: 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WsjSwNFCK1YlWMjQwMABhGMcQyjQFihmACaXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Package size" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Package size", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Middle([Package size],0,Text.Length([Package size])/2)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Middle([Package size],(Text.Length([Package size])/2), Text.Length([Package size]))),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom1",{{"Custom", "Package size 1"}, {"Custom.1", "Package size 2"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Package size"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Package size 1", "Package size"}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns1",{"Package size 2"})
in
    #"Removed Columns1"

[lowei]

I wish I had a better working knowledge of using this... How do I tack that on to the bottom of my existing steps?

#"Inserted Text Length" = Table.AddColumn(#"Removed Columns2", "Length", each Text.Length(Text.From([Package size], "en-US")), Int64.Type),
#"Integer-Divided Column" = Table.TransformColumns(#"Inserted Text Length", {{"Length", each Number.IntegerDivide(_, 2), Int64.Type}}),
#"Renamed Columns3" = Table.RenameColumns(#"Integer-Divided Column",{{"Trim.1.2.1.2", "MF"}})
in
#"Renamed Columns3"