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

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?

  • BA_Pete's avatar
    BA_Pete
    Super User
    3 years ago

     

    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.

    let
    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
        List.Transform(
            {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
        List.Transform(
            {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"})
in
    remOthCols

     

    Pete