topic Re: Getting blank values after the split by column delimiter in DAX Commands and Tips

Getting blank values after the split by column delimiter

Anonymous — Wed, 09 Feb 2022 12:15:41 GMT

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.

Re: Getting blank values after the split by column delimiter

Anonymous — Wed, 09 Feb 2022 12:57:20 GMT

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.

Re: Getting blank values after the split by column delimiter

HotChilli — Wed, 09 Feb 2022 12:45:59 GMT

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

Re: Getting blank values after the split by column delimiter

Anonymous — Wed, 09 Feb 2022 12:50:56 GMT

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

Re: Getting blank values after the split by column delimiter

goncalogeraldes — Wed, 09 Feb 2022 12:52:24 GMT

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