Showing results for 
Search instead for 
Did you mean: 
Helper I
Helper I

Issues with mixed-type field

I'm having several issues with a mixed-type field that I've spent hours trying to fix. 



The first of my issues is that I need a consistent type/fromat ###.###. The second issue is splitting everything for the cells that have a dash into individual rows (340.006, 340.007, and so on) and bringing with it all the data associated wth the origianl row.


Starting with the first issue.   If I convert the field to text, I get what is pictured below. You'll note that the first number should be 340.003 so I can't just extract the first 7 characters.





If I convert them to numeric, understanbly those with the dashes error out, but any number ending with a zero is impacted:



I tried moving anything that was numeric to a separate query so i could work on that issue sepeartely and then bring it all together at the end, but it became difficult to isolate which numbers were no longer in the ###.###  format.  I tried extracting the lenght of column but that converts it into a text field and we're back to having it become a 17 character field as in the second screenshot.


For my second problem, what would be the best way to split anything with a dash into seperate rows so that I go from this: 





To something like this: 



Note - there are several instances of cells with dashes in them.


Thanks in advance for any ideas on how to resolve this.

Super User
Super User

Hi @Kds1113 ,


The following example query converts this:


...into this:



Example query:

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjYx0DMwMFbSUXIEYUelWB2YoAlQwAmEnZAETYECziDsjCRopgumDUE6XEDYBSJpZAmUNNIF04YGQAlXEHZVio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code = _t, someInfo = _t, someOtherInfo = _t]),
    addCategory = Table.AddColumn(Source, "category", each Text.BeforeDelimiter([code], ".")),
    removeCategoryVals = Table.ReplaceValue(addCategory,each [category] & ".", each "",Replacer.ReplaceText,{"code"}),
    splitByDash = Table.SplitColumn(removeCategoryVals, "code", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"code.1", "code.2"}),
    addGenList = Table.AddColumn(splitByDash, "genList", each
            {Number.From([code.1])..Number.From([code.2]) ?? Number.From([code.1])},
            each Text.PadStart(Text.From(_), 3, "0")
    expandGenList = Table.ExpandListColumn(addGenList, "genList"),
    addCodeCalc = Table.AddColumn(expandGenList, "CodeCalc", each [category] & "." & [genList]),
    remOthCols = Table.SelectColumns(addCodeCalc,{"CodeCalc", "someInfo", "someOtherInfo"})



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

@BA_Pete  - once I made the correction to my field, as noted in my message from January 25th, your code worked beautifully in splitting and converting the columsn with dashes.  But I ran into another problem.  I was asked to add another column to my dataset that has a similar structure.  



I tried adding your list gen code to the new column, but it's replicating all the new rows created by the list gen applied to the first column.  Is there a way to next this so the list gen applies to both columns?


Ok. The following code turns this:



...into this:



It's quite a bit longer than before as I've purposely left the steps split out so you can see what's happening. It's basically just running the steps over again on [code2], but then zipping the two lists together so they can go side-by-side, instead of double-expanding.

    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY1LDsAgCAXvwloaUNvUpb8TuDTe/xoFTBMX81jMA+aEEOkiCuBg0CuZlQzL/SqaSpJFKYe6VTFJVqUe6kGbbNvscXitNaXtmk9S82jTbgxi3I+60mGtDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [code1 = _t, code2 = _t, someInfo = _t, someOtherInfo = _t]),
    addCategory1 = Table.AddColumn(Source, "category1", each Text.BeforeDelimiter([code1], ".")),
    removeCategory1Vals = Table.ReplaceValue(addCategory1,each [category1] & ".", each "",Replacer.ReplaceText,{"code1"}),
    splitByDash1 = Table.SplitColumn(removeCategory1Vals, "code1", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"code1.1", "code1.2"}),
    addGenList1 =
    Table.AddColumn(splitByDash1, "genList1", each
            {Number.From([code1.1])..Number.From([code1.2]) ?? Number.From([code1.1])},
            each Text.PadStart(Text.From(_), 3, "0")
    addCategory2 = Table.AddColumn(addGenList1, "category2", each Text.Start([code2], 1)),
    removeCategory2Vals = Table.ReplaceValue(addCategory2, each [category2], each "",Replacer.ReplaceText,{"code2"}),
    splitByDash2 = Table.SplitColumn(removeCategory2Vals, "code2", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"code2.1", "code2.2"}),
    addGenList2 =
    Table.AddColumn(splitByDash2, "genList2", each
            {Number.From([code2.1])..Number.From([code2.2]) ?? Number.From([code2.1])},
            each Text.PadStart(Text.From(_), 2, "0")
    addZipList = Table.AddColumn(addGenList2, "zipList", each List.Zip({[genList1], [genList2]})),
    expandZipListToRows = Table.ExpandListColumn(addZipList, "zipList"),
    extractZipListValues = Table.TransformColumns(expandZipListToRows, {"zipList", each Text.Combine(List.Transform(_, Text.From), ":"), type text}),
    splitZipListByDelim = Table.SplitColumn(extractZipListValues, "zipList", Splitter.SplitTextByEachDelimiter({":"}, QuoteStyle.Csv, false), {"zipList.1", "zipList.2"}),
    addCode1 = Table.AddColumn(splitZipListByDelim, "code1", each Text.Combine({[category1], [zipList.1]}, "."), type text),
    addCode2 = Table.AddColumn(addCode1, "code2", each Text.Combine({[category2], [zipList.2]}, ""), type text),
    remOthCols = Table.SelectColumns(addCode2,{"code1", "code2", "someInfo", "someOtherInfo"})



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Thanks Pete.  Can you walk me through what this is doing in each step.  I tried adjusting it to my query and it does not work.  I think it's because in you example, all the values in "code" are text and I have a combination of text and numeric in the same field.  


So when I get to the xpandGenList field I get the following error on the numeric values: 

Expression.Error: We cannot convert the value 327.00099999999998 to type Text.


Silly question, but have you tried changing the data type to text before doing any transformations?

I assume it's currently showing as 'Any' type to allow both numerical and text types in a single column.



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

I did but it didn't work.  However, I think I found a solution for that problem and can now try your steps for splitting the items with dashes.


Here's what I did:

Transformation = Table.TransformColumns(#"Reordered Columns",{{"code", each try Number.Round(Decimal.From(_),3) otherwise _}}),
#"Changed Type" = Table.TransformColumnTypes(Transformation,{{"code", type text}})


So with this, so now the 327.001 no longer changes to 327.00099999999998 when I covert it to text. Will report back once I add your steps.  



Helpful resources

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors