CODEWITHSUNDEEP
powerbipowerquerym
Nick
Nick

Reputation: 77

PowerQuery to convert a Record into text

Here's something I am needing to do. I am looking to convert a Record into Text/string representation.

For example:-

if a record is

[OrderId = "10001", Owner = "Sam"]

I would like to convert it into a text format like this:

"[OrderId = \"10001\", Owner = \"Sam\"]"

Any help is greatly appreciated!

Upvotes: 0

Views: 2775

Answers (2)

horseyride
horseyride

Reputation: 21318

try this, works for any # of fields

let x=[OrderId = "10001", Owner = "Sam"] ,
z="""["&Text.Combine(Table.AddColumn(Record.ToTable(x), "Custom", each [Name]&" = \"""&[Value])[Custom],"\"", ")&"\""]"""
in z

Upvotes: 1

Umut K
Umut K

Reputation: 1388

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WivYvSkkt8kxRsFWIUTI0MDAwjFHSUfAvz0stAgsFJ+bGKMUqxcYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Record = _t]),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Record", Splitter.SplitTextByDelimiter("""", QuoteStyle.None), {"Record.1", "Record.2", "Record.3", "Record.4", "Record.5"}),
    #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter", "Custom", each Character.FromNumber(34) 
& [Record.1] 
& "\" & Character.FromNumber(34) 
&[Record.2]
& "\" & Character.FromNumber(34) 
&[Record.3]
& "\" & Character.FromNumber(34) 
&[Record.4]
& "\" & Character.FromNumber(34) 
& [Record.5]
& Character.FromNumber(34))
in
    #"Added Custom"

if the record comes as below , it is another question...

OrderID Owner
100001 Sam

Upvotes: 1

Related Questions