Split a column and sort

[Anonymous]

Hello there. Hope everyone is well. Please see below... Would be great if someone hepled me with this.

Current Situation: All my data is in one column.

A00

A00.0

A00.1

A00.9

A01

A01.0

A01.1

A01.2

Desired Outcome:

A00	A00.0
A00	A00.1
A00	A00.9
A01	A01.0
A01	A01.1
A01	A01.2

Does anyone know how to archieve this split and allocation in PowerBI?

[v-jayw-msft · Accepted solution]

Hi @Anonymous ,

You can do this in Query Editor, check the query below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwUIrVAdN6CJYhnGUJZcFEDOGqDPUQYkZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Column1", "Column1 - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Column1 - Copy", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Column1 - Copy.1", "Column1 - Copy.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1 - Copy.1", type text}, {"Column1 - Copy.2", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type1", each [#"Column1 - Copy.2"] <> null and [#"Column1 - Copy.2"] <> ""),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1 - Copy.2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Column1 - Copy.1", "Column1"})
in
    #"Reordered Columns"

You will need to dumplicate the column first then use split column feature then remove empty rows and at lase remove extra cloumn.

Pbix as attached.

Best Regards,

Jay

[amitchandak]

@Anonymous ,

refer

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

https://docs.microsoft.com/en-us/powerquery-m/text-contains

In M

if text-contains([column],'.') then [column] else [column] & ".0"

how to group data

https://www.youtube.com/watch?v=pSIqhYR2HRU