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

[muhammad susanto]

hi all..

how to split data using Power BI (Desktop) with criteria:
split data after mark (/) slash
here my sample

original data	split1	split2	spit3	split3	split4
Text AAA/Mango/1. Tim Kerja/1b	Text AAA	Mango	1. Tim Kerja	1b
Text AAA/Mango	Text AAA	Mango
Text AAA/Mango/2. xxx	Text AAA	Mango	2.xxx
Text AAA/Mango/2. xxx/2a	Text AAA	Mango	2.xxx	2a
Text AAA/Mango/3. YYY/3a/ok	Text AAA	Mango	3.YYY	3a	ok

anyone help, thank in advance
susant

 

[JEC]

You should do this transformation in the Power Query editor, before you load it into Power BI

 

[muhammad susanto]

Hi...
What, s formula ini PowerQuery.? I hope someone help me that formula.

 

[JGordon11]

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
	A	B	C	D	E	F	G	H
1	original data		Split1	Split2	Split3	Split4	Split5
2	Text AAA/Mango/1. Tim Kerja/1b		Text AAA	Mango	1. Tim Kerja	1b
3	Text AAA/Mango		Text AAA	Mango
4	Text AAA/Mango/2. xxx		Text AAA	Mango	2. xxx
5	Text AAA/Mango/2. xxx/2a		Text AAA	Mango	2. xxx	2a
6	Text AAA/Mango/3. YYY/3a/ok		Text AAA	Mango	3. YYY	3a	ok
7
Sheet1

 

[muhammad susanto]

hi JGordon11, thank you very much!!

​

 

[muhammad susanto]

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
	A	B	C	D	E	F	G	H
1	original data		Split1	Split2	Split3	Split4	Split5
2	Text AAA/Mango/1. Tim Kerja/1b		Text AAA	Mango	1. Tim Kerja	1b
3	Text AAA/Mango		Text AAA	Mango
4	Text AAA/Mango/2. xxx		Text AAA	Mango	2. xxx
5	Text AAA/Mango/2. xxx/2a		Text AAA	Mango	2. xxx	2a
6	Text AAA/Mango/3. YYY/3a/ok		Text AAA	Mango	3. YYY	3a	ok
7
Sheet1

hi...
when i change data source using google sheet the formula not work. here complete formula

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?