Getting blank values after the split by column delimiter

[Anonymous]

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.

[goncalogeraldes]

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

[HotChilli · Accepted solution]

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]

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

[Anonymous]

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.