Forum Discussion
split column by varying length per row based off character count
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"})
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!
8 Replies
- AlB
Community Champion
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"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"
- tarunsingla
Solution Sage
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
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,
TarunMy 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
