Forum Discussion

dommyw277's avatar
dommyw277
Icon for Helper V rankHelper V
9 months ago
Solved

Splitting Column values

Hi, how would i split a column like this:   Category1 SR-01 SR-02 SR-03 Category 2 SR-05 INC-01 INC-02 Category 3 INC-03 SR-06 etc etc
Need Help
  • V-yubandi-msft's avatar
    V-yubandi-msft
    8 months ago

    Hi dommyw277 ,

    Thanks for the screenshot  that really helps. As per my guess, the Invalid identifier error is coming from the way the column name Inc/SR is being referenced. Because the column name contains a slash (/), Power Query requires the #""  format.

    So anywhere you have- [Inc/SR]

    please change it to - [#"Inc/SR"]

    And in your filter step

    each [#"Inc/SR"] <> null and [#"Inc/SR"] <> ""

    Once the column is referenced with [#"...."] , the syntax error should be resolved.


    Hope this helps please give it a try. If anything still comes up, feel free to share another screenshot and we can take a look.

Praful_Potphode's avatar
Praful_Potphode
Icon for Super User rankSuper User
9 months ago

Hi dommyw277 ,

 

try Sample PBIX and let us know.

M QUery code looks like below:

let
    Source = Table.FromRows(
        {
            {"Category1"},
            {"SR-01"},
            {"SR-02"},
            {"SR-03"},
            {"Category 2"},
            {"SR-05"},
            {"INC-01"},
            {"INC-02"},
            {"Category 3"},
            {"INC-03"},
            {"SR-06"}
        },
        type table [Column1 = text]
    ),//replace this step with your source step
    AddedCategory = Table.AddColumn(Source, "Category", each 
        if Text.Contains([Column1], "-") then null else [Column1]
    ),
    FilledDown = Table.FillDown(AddedCategory, {"Category"}),
    FilteredRows = Table.SelectRows(FilledDown, each Text.Contains([Column1], "-")),
    RenamedColumns = Table.RenameColumns(FilteredRows, {{"Column1", "Code"}})
in
    RenamedColumns

Please give kudos or mark it as solution once confirmed.

 

Thanks and Regards,

Praful