Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
HI All.
I am new to BI.
I have below text in a column of a table, I want to extract the Name,ID,City into different columns
Duplicate values also there in my string.
tx:StudentID:24tgfg, tx:Name:Chetan, tx:City:Austin, tx:state:TX,tx:Sub:jggjgjgj,tx:Org:hjjhgjjgjhgj,tx:BU:ewrwrrwrwrwr,tx:StudentID:249340, tx:Name:Kiran, tx:City:Austin, tx:state:TX,
Can some one help
Appreciate your support here
Solved! Go to Solution.
Hi @KiranGupta15 ,
I can get you this far, but I need more info from you. I turned this:
into this:
using this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKqmwCi4pTUnNK/F0sTIyKUlPS9dRAAr6JeamWjlnpJYk5oH5zpkllVaOpcUlmRB+cUliSapVSIQOyIDSJKus9PQsEATx/YvSrTKysjLSs4ACGRAxp1Cr1PKi8qKicjDUQbPX0tjEAGGvd2YRIWuVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("tx:", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12"}),
#"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter1", each ([Column1.2] <> null)),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"Column1.2", each Text.BeforeDelimiter(_, ","), type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Extracted Text Before Delimiter",{{"Column1.1", Text.Proper, type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Capitalized Each Word", each ([Column1.1] = "City" or [Column1.1] = "Name" or [Column1.1] = "Studentid"))
in
#"Filtered Rows1"
However, I cannot get the Name, ID, and City into columns because there is nothing to tell me what student, ID, and city are associated with each other, unless I just assume it is in sequential order, in which case I can get this:
using this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKqmwCi4pTUnNK/F0sTIyKUlPS9dRAAr6JeamWjlnpJYk5oH5zpkllVaOpcUlmRB+cUliSapVSIQOyIDSJKus9PQsEATx/YvSrTKysjLSs4ACGRAxp1Cr1PKi8qKicjDUQbPX0tjEAGGvd2YRIWuVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("tx:StudentID", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
#"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.2", "Column1.3"}),
#"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
#"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 0, 1, Int64.Type),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Added Index", "Column1", Splitter.SplitTextByDelimiter("tx:", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter1", {"Index"}, "Attribute", "Value"),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter2","","StudentID",Replacer.ReplaceValue,{"Value.1"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Replaced Value", {{"Value.2", each Text.BeforeDelimiter(_, ","), type text}}),
#"Filtered Rows" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Value.1] = "City" or [Value.1] = "Name" or [Value.1] = "StudentID")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Index", "Value.1", "Value.2"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns1", List.Distinct(#"Removed Other Columns1"[Value.1]), "Value.1", "Value.2"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns"
If that is not what you need, please be a bit more specific on the expected results, and explain how I am to read that sample record you posted.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
If you need to post more data to make it clearer, please see the links below on putting tables in the posts.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHere is a different approach in the query editor. As @edhans suggested, it depends on the consistency of your data. If the values are all sequential and there is a repeating pattern of 7 values, this should work for you to get the result shown below.
To see how it works, just create a blank query, go to Advanced Editor, and replace the text there with the M code below.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKqmwCi4pTUnNK/F0sTIyKUlPS9dRAAr6JeamWjlnpJYk5oH5zpkllVaOpcUlmRB+cUliSapVSIQOyIDSJKus9PQsEATx/YvSrTKysjLSs4ACGRAxp1Cr1PKi8qKicjDUQbPX0tjEAGGvd2YRIWuVYmMB", 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}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Split([Column1], ",")),
#"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
#"Expanded Custom" = Table.ExpandListColumn(#"Removed Other Columns", "Custom"),
#"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Custom", "Custom", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Custom.1", type text}, {"Custom.2", type text}, {"Custom.3", type text}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each ([Custom.3] <> null)),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1"}),
#"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Custom.2", Text.Trim, type text}, {"Custom.3", Text.Trim, type text}}),
#"Added Index" = Table.AddIndexColumn(#"Trimmed Text", "Index", 1, 1, Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each Number.RoundUp([Index]/7)),
#"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[Custom.2]), "Custom.2", "Custom.3"),
#"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Custom"})
in
#"Removed Columns2"
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi @KiranGupta15 ,
I can get you this far, but I need more info from you. I turned this:
into this:
using this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKqmwCi4pTUnNK/F0sTIyKUlPS9dRAAr6JeamWjlnpJYk5oH5zpkllVaOpcUlmRB+cUliSapVSIQOyIDSJKus9PQsEATx/YvSrTKysjLSs4ACGRAxp1Cr1PKi8qKicjDUQbPX0tjEAGGvd2YRIWuVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("tx:", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11", "Column1.12"}),
#"Transposed Table" = Table.Transpose(#"Split Column by Delimiter"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Transposed Table", "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
#"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter1", each ([Column1.2] <> null)),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"Column1.2", each Text.BeforeDelimiter(_, ","), type text}}),
#"Capitalized Each Word" = Table.TransformColumns(#"Extracted Text Before Delimiter",{{"Column1.1", Text.Proper, type text}}),
#"Filtered Rows1" = Table.SelectRows(#"Capitalized Each Word", each ([Column1.1] = "City" or [Column1.1] = "Name" or [Column1.1] = "Studentid"))
in
#"Filtered Rows1"
However, I cannot get the Name, ID, and City into columns because there is nothing to tell me what student, ID, and city are associated with each other, unless I just assume it is in sequential order, in which case I can get this:
using this code:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKqmwCi4pTUnNK/F0sTIyKUlPS9dRAAr6JeamWjlnpJYk5oH5zpkllVaOpcUlmRB+cUliSapVSIQOyIDSJKus9PQsEATx/YvSrTKysjLSs4ACGRAxp1Cr1PKi8qKicjDUQbPX0tjEAGGvd2YRIWuVYmMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
#"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByDelimiter("tx:StudentID", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3"}),
#"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.2", "Column1.3"}),
#"Transposed Table" = Table.Transpose(#"Removed Other Columns"),
#"Added Index" = Table.AddIndexColumn(#"Transposed Table", "Index", 0, 1, Int64.Type),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Added Index", "Column1", Splitter.SplitTextByDelimiter("tx:", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Split Column by Delimiter1", {"Index"}, "Attribute", "Value"),
#"Split Column by Delimiter2" = Table.SplitColumn(#"Unpivoted Other Columns", "Value", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Value.1", "Value.2"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter2","","StudentID",Replacer.ReplaceValue,{"Value.1"}),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Replaced Value", {{"Value.2", each Text.BeforeDelimiter(_, ","), type text}}),
#"Filtered Rows" = Table.SelectRows(#"Extracted Text Before Delimiter", each ([Value.1] = "City" or [Value.1] = "Name" or [Value.1] = "StudentID")),
#"Removed Other Columns1" = Table.SelectColumns(#"Filtered Rows",{"Index", "Value.1", "Value.2"}),
#"Pivoted Column" = Table.Pivot(#"Removed Other Columns1", List.Distinct(#"Removed Other Columns1"[Value.1]), "Value.1", "Value.2"),
#"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
#"Removed Columns"
If that is not what you need, please be a bit more specific on the expected results, and explain how I am to read that sample record you posted.
How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.
If you need to post more data to make it clearer, please see the links below on putting tables in the posts.
How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@KiranGupta15 - Not sure, doesn't look fun at all. What is the expected result of that? This is something that you likely want to do in Power Query. @ImkeF , @edhans do you have any magic for this poor soul?
Hi Greg, I too have the same situaton where i have a column in excel which is a output of a powershell script which has lot of comma separated values. I need to get the workspace name. Here one important thing to note, i dont have same set of key value pairs in all the columns, Could you suggest how to extract only one key value pair using power bi.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |