Issues with mixed-type field

Question

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.

ba_pete · Answer

Hi&nbsp;Kds1113&nbsp;,
&nbsp;
The following example query converts this:

...into this:

&nbsp;
Example query:
let
    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] &amp; ".", each "",Replacer.ReplaceText,{"code"}),
    splitByDash = Table.SplitColumn(removeCategoryVals, "code", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"code.1", "code.2"}),
    addGenList = Table.AddColumn(splitByDash, "genList", each
        List.Transform(
            {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] &amp; "." &amp; [genList]),
    remOthCols = Table.SelectColumns(addCodeCalc,{"CodeCalc", "someInfo", "someOtherInfo"})
in
    remOthCols
&nbsp;
Pete

kds1113 · Answer

Thanks Pete.&nbsp; Can you walk me through what this is doing in each step.&nbsp; I tried adjusting it to my query and it does not work.&nbsp; 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.&nbsp;&nbsp;So when I get to the xpandGenList field I get the following error on the numeric values:&nbsp;Expression.Error: We cannot convert the value 327.00099999999998 to type Text.Details:Value=327.001Type=[Type]

ba_pete · Answer

&nbsp;
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.
&nbsp;
Pete

kds1113 · Answer

I did but it didn't work.&nbsp; However, I think I found a solution for that problem and can now try your steps for splitting the items with dashes.&nbsp;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}})&nbsp;So with this, so now the 327.001 no longer changes to&nbsp;327.00099999999998 when I covert it to text. Will report back once I add your steps.&nbsp;&nbsp;&nbsp;thanks!

kds1113 · Answer

BA_Pete&nbsp; - 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.&nbsp; But I ran into another problem.&nbsp; I was asked to add another column to my dataset that has a similar structure.&nbsp;&nbsp;&nbsp;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.&nbsp; Is there a way to next this so the list gen applies to both columns?

Forum Discussion

Issues with mixed-type field

6 Replies

Related content

PowerBI Dashboard Issue on date field

columns with mixed data types

how to change column into mixed data type

Averagex issue

columns with mixed data types