Power BI : Split Data After Multiple Mark "/" (Slash)

muhammad susanto

Well-known Member
Joined
Jan 8, 2013
Messages
2,089
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
hi all..

how to split data using Power BI (Desktop) with criteria:
split data after mark (/) slash
here my sample
original datasplit1split2spit3split3split4
Text AAA/Mango/1. Tim Kerja/1bText AAAMango1. Tim Kerja1b
Text AAA/MangoText AAAMango
Text AAA/Mango/2. xxxText AAAMango2.xxx
Text AAA/Mango/2. xxx/2aText AAAMango2.xxx2a
Text AAA/Mango/3. YYY/3a/okText AAAMango3.YYY3aok


anyone help, thank in advance
susant
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
You should do this transformation in the Power Query editor, before you load it into Power BI
 
Upvote 0
Power Query:
let
    lst = Table.ToColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]){0},
    n = List.Max(List.Transform(lst, each Text.Length(_)-Text.Length(Text.Remove(_,"/"))))+1,
    cols = List.Accumulate({1..n},{},(s,c)=> s & {"Split" & Text.From(c)}),
    Result = Table.SplitColumn(Table.FromColumns({lst}), "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), cols)
in
    Result

Book1
ABCDEFGH
1original dataSplit1Split2Split3Split4Split5
2Text AAA/Mango/1. Tim Kerja/1bText AAAMango1. Tim Kerja1b
3Text AAA/MangoText AAAMango
4Text AAA/Mango/2. xxxText AAAMango2. xxx
5Text AAA/Mango/2. xxx/2aText AAAMango2. xxx2a
6Text AAA/Mango/3. YYY/3a/okText AAAMango3. YYY3aok
7
Sheet1
 
Upvote 0
Solution
Power Query:
let
    lst = Table.ToColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]){0},
    n = List.Max(List.Transform(lst, each Text.Length(_)-Text.Length(Text.Remove(_,"/"))))+1,
    cols = List.Accumulate({1..n},{},(s,c)=> s & {"Split" & Text.From(c)}),
    Result = Table.SplitColumn(Table.FromColumns({lst}), "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), cols)
in
    Result

Book1
ABCDEFGH
1original dataSplit1Split2Split3Split4Split5
2Text AAA/Mango/1. Tim Kerja/1bText AAAMango1. Tim Kerja1b
3Text AAA/MangoText AAAMango
4Text AAA/Mango/2. xxxText AAAMango2. xxx
5Text AAA/Mango/2. xxx/2aText AAAMango2. xxx2a
6Text AAA/Mango/3. YYY/3a/okText AAAMango3. YYY3aok
7
Sheet1
hi...
when i change data source using google sheet the formula not work. here complete formula
VBA Code:
let
    Source = GoogleSheets.Contents("https://docs.google.com/spreadsheets/d/1b32K9EmZ4oGjHrpN3z2AjqQhZOUZoukZD5M7boVR3Gw"),
    #"list file_Table" = Source{[name="list file",ItemKind="Table"]}[Data]
    n = List.Max(List.Transform(lst, each Text.Length(_)-Text.Length(Text.Remove(_,"/"))))+1,
    cols = List.Accumulate({1..n},{},(s,c)=> s & {"Split" & Text.From(c)}),
    Result = Table.SplitColumn(Table.FromColumns({lst}), "Column1", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), cols)
in
Result
how to fix this problem?
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top