Forum Discussion

Till__'s avatar
Till__
Helper I
3 years ago

Splitting Columns without generating new column

Dear Community,    I want to split serveral columns after if it changes from number to text. For example the entry is 2xyx and I only want to have the 2 left over. Test 2xyx Normaly a ne...
Need Help
PwerQueryKees's avatar
PwerQueryKees
Super User
1 year ago

Back at my laptop.

 

Started with this test data:

 

This query:

 

let
    Source = YourDataSource,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Any Column 1", "Any Column 2"}, "Attribute", "Value"),  // Here you mention the OTHER columns. Generetae it in the UI by selecting the other columns and select "UnPivot Other Columns"
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), {"Attribute"}), // The split. I used the UI to generate the split and then removed the second column manually
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

 

The unpivot was created in the UI and changed manually.

The big advantage of the Table.UnpivotOtherColumns is that additional columns will be taken care of automatically.

 

The Table.Pivot was created in UI like this:

Results in this:

Same data, different column names.

2 changes: Rows and columns are both sorted....

 

  • PwerQueryKees's avatar
    PwerQueryKees
    Super User
    1 year ago

    I just realized I misread. I was splitting the column names, but you need the values to be split.

     

    Works almost the same:

    let
    Source = YourDataSource,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Any Column 1", "Any Column 2"}, "Attribute", "Value"),  // Here you mention the OTHER columns. Generetae it in the UI by selecting the other columns and select "UnPivot Other Columns"
    #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter(")", QuoteStyle.Csv), {"Value"}), // The split. I used the UI to generate the split and then removed the second column manually
    #"Pivoted Column" = Table.Pivot(#"Split Column by Delimiter", List.Distinct(#"Split Column by Delimiter"[Attribute]), "Attribute", "Value")
in
    #"Pivoted Column"

     

    to go from this:

    to this