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            ...
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

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