Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
Hello there @THENNA_41 ! 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
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |