Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
KiranGupta15
Frequent Visitor

Key Value pair values

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 

1 ACCEPTED SOLUTION
edhans
Super User
Super User

Hi @KiranGupta15 ,

 

I can get you this far, but I need more info from you. I turned this:

edhans_0-1598650469803.png

into this:

edhans_1-1598650496019.png

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:

edhans_2-1598650974840.png

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

4 REPLIES 4
mahoneypat
Employee
Employee

Here 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.

 

mahoneypat_0-1598661145289.png

 

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"




Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


edhans
Super User
Super User

Hi @KiranGupta15 ,

 

I can get you this far, but I need more info from you. I turned this:

edhans_0-1598650469803.png

into this:

edhans_1-1598650496019.png

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:

edhans_2-1598650974840.png

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Greg_Deckler
Super User
Super User

@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?


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.