Forum Discussion
How to split column which contains Dot (.) separated values ???
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
5 Replies
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 GuThanks 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
- 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.
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
Sorry - I did not notice you need to select items from each group.
