Forum Discussion

Kds1113's avatar
Kds1113
Helper I
3 years ago

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...
Need Help
BA_Pete's avatar
BA_Pete
Super User
3 years ago

Hi Kds1113 ,

 

The following example query converts this:

...into this:

 

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] & ".", 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] & "." & [genList]),
    remOthCols = Table.SelectColumns(addCodeCalc,{"CodeCalc", "someInfo", "someOtherInfo"})
in
    remOthCols

 

Pete

Kds1113's avatar
Kds1113
Helper I
3 years ago

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.
Details:
Value=327.001
Type=[Type]

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

     

    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.

     

    Pete

    • Kds1113's avatar
      Kds1113
      Helper I
      3 years ago

      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.  

       

      thanks!