Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Getting blank values after the split by column delimiter

Hi Team, i have the following data table .  

 

Month                    parameter                           Index 

April              TAR LOW,SN HIGH,                          5

March              SN HIGH,CO HIGH,                      15

May                      CO LOW,                                 35

 

 above table parameter column i did the   have split column by Delimiter . please find the below screen shot reference 

 

 

after done this . i am getting  below output.

Month                    parameter                           Index 

April                         TAR LOW                            5
April                         SN HIGH                            5
April                                                                    5

March                     SN HIGH                            15
March                     CO HIGH                            15
March                                                                15

May                      CO LOW                                35
May                                                                   35

 

i am getting everyrow duplicate blank row. how it will resolved . thanks in advance.

Help appreciated
Help me
Help Requested
  • HotChilli's avatar
    HotChilli
    4 years ago

    You have many options. (remove the last character of parameter column with a formula etc.)

     

    The simplest would be to filter the blank values out (do this from the dropdown at the top of the column) after the split

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

     

    From what we can see, it seems that your column has been split by rows and each split part was placed into a different row causing duplicates and due to the trailing comma, you are getting a blank row.

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    You have many options. (remove the last character of parameter column with a formula etc.)

     

    The simplest would be to filter the blank values out (do this from the dropdown at the top of the column) after the split

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      HotChilli  thanks i did the same . i filter the blank value

  • goncalogeraldes's avatar
    goncalogeraldes
    Icon for Super User rankSuper User
    4 years ago

    Hello there Anonymous ! Why not just filter out the blank rows? You dont seem to lose information if you do so...

     

    let
    Source = Excel.Workbook(File.Contents("C:\Users\Book1.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    Trim_Text = Table.TransformColumns(Table1_Table,{{"parameter", Text.Trim, type text}}),
    Split_Column_Comma = Table.ExpandListColumn(
        Table.TransformColumns(
            Trim_Text, 
            {{"parameter", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), 
                let itemType = (type nullable text) meta [Serialized.Text = true] 
                in type {itemType}}}), 
            "parameter"),
    Filter_Blanks = Table.SelectRows(Split_Column_Comma, each ([parameter] <> ""))
in
    Filter_Blanks

     

    Hope this answer solves your problem!
    If you need any additional help please @ me in your reply.
    If my reply provided you with a solution, please consider marking it as a solution ✔️ or giving it a kudoe 👍
    Thanks!

    You can also check out my LinkedIn!

    Best regards,
    Gonçalo Geraldes