DirectQuery - Split into Rows

[kilala]

Hi all,

I know that we cannot implement split into rows in directquery mode, but is there a workaround i can follow? 

The examples i found is only split into column, not rows. if anyone can find any artcle or used to do it, appreciate if you can share the article here.

Basically, i just want to split these by delimiter ";".

e.g:

1 row = A;B;C

result:

3 rows

A

B

C

[Dangar332]

Hi, @kilala 

here sample data

follow below steps

1.  in home tab click on split column after click  By delimeter

2. click ok and it give you output like below

3. transpose columns it give you answer like below

download pbix file click HERE 

refer power query part

[Dangar332]

hi, @kilala 

try below code in power query

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMrQ2sjZWio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [row = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"row", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "row", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"row.1", "row.2", "row.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"row.1", Int64.Type}, {"row.2", Int64.Type}, {"row.3", Int64.Type}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1")
in
    #"Transposed Table"