How to split column which contains Dot (.) separated values ???

[Raghavendra]

HI , I have a column "MYColumn" which has Dot separated values.My Requirement is to split "Mycolumn" into 3 columns as shown in below image

 

Thanks, Raghav

[v-yulgu-msft]

Hi, @Raghavendra

In this thread, you should know that PowerBI is a tool to design report rather than process string. As for your requirement, there is no simple and direct way to achieve that, you can take the following workaround as a reference.

Pasete the below Power Query in Advanced Editor.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WStRL0kvWSdFL1UvTSdfL0MvUydLL1stRitWByoFZWORjAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MYColumn = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"MYColumn", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type","MYColumn",Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv),{"MYColumn.1", "MYColumn.2", "MYColumn.3", "MYColumn.4"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"MYColumn.1", type text}, {"MYColumn.2", type text}, {"MYColumn.3", type text}, {"MYColumn.4", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","MYColumn.1",Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),{"MYColumn.1.1", "MYColumn.1.2", "MYColumn.1.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"MYColumn.1.1", type text}, {"MYColumn.1.2", type text}, {"MYColumn.1.3", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2","MYColumn.2",Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),{"MYColumn.2.1", "MYColumn.2.2", "MYColumn.2.3"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"MYColumn.2.1", type text}, {"MYColumn.2.2", type text}, {"MYColumn.2.3", type text}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3","MYColumn.3",Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),{"MYColumn.3.1", "MYColumn.3.2", "MYColumn.3.3"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"MYColumn.3.1", type text}, {"MYColumn.3.2", type text}, {"MYColumn.3.3", type text}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type4","MYColumn.4",Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv),{"MYColumn.4.1", "MYColumn.4.2", "MYColumn.4.3"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"MYColumn.4.1", type text}, {"MYColumn.4.2", type text}, {"MYColumn.4.3", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type5", "Index", 0, 1),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "Custom", each Text.Range([Attribute],9,1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each Text.Range([Attribute],11,1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Attribute"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[Custom.1]), "Custom.1", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{{"1", "column1"}, {"2", "column2"}, {"3", "column3"}})
in
    #"Renamed Columns"

Create a new table using below DAX formula:

Table 2 = 
SUMMARIZE(Table1,Table1[Index],
"Column1",CONCATENATEX(Table1,Table1[column1],",",Table1[Custom],ASC),
"Column2",CONCATENATEX(Table1,Table1[column2],",",Table1[Custom],ASC),
"Column3",CONCATENATEX(Table1,Table1[column3],",",Table1[Custom],ASC)
)

Best regards,
Yuliana Gu

[Raghavendra]

Thanks Yuliana_Gu for the reply.

I  have wrote below power query and able to achieve the desired output.But here one more thing i need to resolve .below query is working for unique values not for the duplicate values., we need to  eliminate the duplicate values  in the column1,column2,column3.Currently i am working on it.if you have any idea please help me on this.

Please find below steps which is taking care of unique values. 

1)  Replaced the dot (.) symbol with "|" symbol.

2)  created custom column and wrote a Query to find the number of ","(Comma's) in the column.

3) Replace the "," value with "|" symbol.

4) Created 3 custom columns and wrote Query to retrieve  values for Column1,Column2,Column3 

Query

==============

let
Source = Excel.Workbook(File.Contents("C:\Users\Ram\Desktop\Example2.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}, {"Column2", type text}}),
#"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),
#"Replaced Value" = Table.ReplaceValue(#"Promoted Headers",".","|",Replacer.ReplaceText,{"Room"}),
#"Added Custom" = Table.AddColumn(#"Replaced Value", "Count", each List.Count(Text.Split([Room],","))-1),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "SplitRoom", each Text.Replace([Room],",","|")),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "RoomCatagory_A", each if [Count] = 0 then Text.Split([SplitRoom],"|"){0} else "" & (if [Count] = 1 then Text.Split([SplitRoom],"|"){0} & ", " & Text.Split([SplitRoom],"|"){3} else "")
& (if [Count] = 2 then Text.Split([SplitRoom],"|"){0} & ", " & Text.Split([SplitRoom],"|"){3} & ", " & Text.Split([SplitRoom],"|"){6} else "")
& (if [Count] = 3 then Text.Split([SplitRoom],"|"){0} & ", " & Text.Split([SplitRoom],"|"){3} & ", " & Text.Split([SplitRoom],"|"){6} & ", " & Text.Split([SplitRoom],"|"){9} else "")
& (if [Count] = 4 then Text.Split([SplitRoom],"|"){0} & ", " & Text.Split([SplitRoom],"|"){3} & ", " & Text.Split([SplitRoom],"|"){6} & ", " & Text.Split([SplitRoom],"|"){9} & ", " & Text.Split([SplitRoom],"|"){12} else "")
& (if [Count] = 5 then Text.Split([SplitRoom],"|"){0} & ", " & Text.Split([SplitRoom],"|"){3} & ", " & Text.Split([SplitRoom],"|"){6} & ", " & Text.Split([SplitRoom],"|"){9} & ", " & Text.Split([SplitRoom],"|"){12} & ", " & Text.Split([SplitRoom],"|"){15} else "")),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "RoomCategory_B", each if [Count] = 0 then Text.Split([SplitRoom],"|"){1} else "" & (if [Count] = 1 then Text.Split([SplitRoom],"|"){1} & ", " & Text.Split([SplitRoom],"|"){4} else "")
& (if [Count] = 2 then Text.Split([SplitRoom],"|"){1} & ", " & Text.Split([SplitRoom],"|"){4} & ", " & Text.Split([SplitRoom],"|"){7} else "")
& (if [Count] = 3 then Text.Split([SplitRoom],"|"){1} & ", " & Text.Split([SplitRoom],"|"){4} & ", " & Text.Split([SplitRoom],"|"){7} & ", " & Text.Split([SplitRoom],"|"){10} else "")
& (if [Count] = 4 then Text.Split([SplitRoom],"|"){1} & ", " & Text.Split([SplitRoom],"|"){4} & ", " & Text.Split([SplitRoom],"|"){7} & ", " & Text.Split([SplitRoom],"|"){10} & ", " & Text.Split([SplitRoom],"|"){13} else "")
& (if [Count] = 5 then Text.Split([SplitRoom],"|"){1} & ", " & Text.Split([SplitRoom],"|"){4} & ", " & Text.Split([SplitRoom],"|"){7} & ", " & Text.Split([SplitRoom],"|"){10} & ", " & Text.Split([SplitRoom],"|"){13} & ", " & Text.Split([SplitRoom],"|"){16} else "")),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "RoomCategory_C", each if [Count] = 0 then Text.Split([SplitRoom],"|"){2} else "" & (if [Count] = 1 then Text.Split([SplitRoom],"|"){2} & ", " & Text.Split([SplitRoom],"|"){5} else "")
& (if [Count] = 2 then Text.Split([SplitRoom],"|"){2} & ", " & Text.Split([SplitRoom],"|"){5} & ", " & Text.Split([SplitRoom],"|"){8} else "")
& (if [Count] = 3 then Text.Split([SplitRoom],"|"){2} & ", " & Text.Split([SplitRoom],"|"){5} & ", " & Text.Split([SplitRoom],"|"){8} & ", " & Text.Split([SplitRoom],"|"){11} else "")
& (if [Count] = 4 then Text.Split([SplitRoom],"|"){2} & ", " & Text.Split([SplitRoom],"|"){5} & ", " & Text.Split([SplitRoom],"|"){8} & ", " & Text.Split([SplitRoom],"|"){11} & ", " & Text.Split([SplitRoom],"|"){14} else "")
& (if [Count] = 5 then Text.Split([SplitRoom],"|"){2} & ", " & Text.Split([SplitRoom],"|"){5} & ", " & Text.Split([SplitRoom],"|"){8} & ", " & Text.Split([SplitRoom],"|"){11} & ", " & Text.Split([SplitRoom],"|"){14} & ", " & Text.Split([SplitRoom],"|"){17} else ""))
in
#"Added Custom4"

My Output

============

Thanks,

Raghav

[mpo]

- Navigate to "Edit query" window.

- Select your table, click on the "MYColumn" header.

- Make sure "Home" tab is selected, click "Split Column" -> "By delimiter",

- Select "Comma" in the dropdown and "At each occurrence of the delimiter" option.

- For each of the resulting columns, on the same "Home" tab select "Replace Values".

- Value To Find: ".", Replace With: ","

- Now you should see your desired result.

[Raghavendra]

Hi,

By your suggestion i am getting 5 columns as  below  image 

but my desigred output is it should be 3 columns and each column should separated with comma values.

 

Thanks,

Raghav

[mpo]

Sorry - I did not notice you need to select items from each group.