Forum Discussion
Splitting PostgreSQL hstore fields
I have a column in my PostgeSQL data that is an hstore field. It contains data from our accounting software for user defined fields. I cannot use the split command because all data is not always there. The hstore data includes the field label and the data. See below that the label is contained inside "" and then there is an => and then the data itself inside""
I want to create a custom column for each field. For instance I want a column called 'Carrier' that contains the carrier name.
UDF DATA
| "Passed"=>"TRUE", "Manager"=>"Gerry" |
| "Date"=>"2016-11-24", "Carrier"=>"Fedex", "TrackNo"=>"465565656", "Complete"=>"TRUE" |
| "Date"=>"2016-11-23", "Carrier"=>"UPS", "TrackNo"=>"668877" |
| "Carrier"=>"UPS", "Complete"=>"TRUE" |
| "By"=>"On time", "Date"=>"2016-05-24", "Carrier"=>"Fedex", "TrackNo"=>"1123", "Complete"=>"TRUE" |
Hi alromeyn,
You can refer to below steps to get the carrier name:
Source:
Formula:
let
Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\xxxxx\Desktop\New Text Document.txt"), null, null, 1252)}),
#"Replaced Value" = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"Column1"}),//remove the space character
#"Added Custom1" = Table.AddColumn(#"Replaced Value", "Carrier", each List.Select(Text.Split([Column1],","), each Text.Contains(_, "Carrier"))),//split column and select the record which contain "carrier"
#"Expanded Temp" = Table.ExpandListColumn(#"Added Custom1", "Carrier"),//expandlist
#"Replaced Value1" = Table.ReplaceValue(Table.ReplaceValue(#"Expanded Temp","""","",Replacer.ReplaceText,{"Carrier"}),"Carrier=>","",Replacer.ReplaceText,{"Carrier"})//remove " and "carrier=>"
in
#"Replaced Value1"
Regards,Xiaoxin Sheng
2 Replies
Hi alromeyn,
You can refer to below steps to get the carrier name:
Source:
Formula:
let
Source = Table.FromColumns({Lines.FromBinary(File.Contents("C:\Users\xxxxx\Desktop\New Text Document.txt"), null, null, 1252)}),
#"Replaced Value" = Table.ReplaceValue(Source," ","",Replacer.ReplaceText,{"Column1"}),//remove the space character
#"Added Custom1" = Table.AddColumn(#"Replaced Value", "Carrier", each List.Select(Text.Split([Column1],","), each Text.Contains(_, "Carrier"))),//split column and select the record which contain "carrier"
#"Expanded Temp" = Table.ExpandListColumn(#"Added Custom1", "Carrier"),//expandlist
#"Replaced Value1" = Table.ReplaceValue(Table.ReplaceValue(#"Expanded Temp","""","",Replacer.ReplaceText,{"Carrier"}),"Carrier=>","",Replacer.ReplaceText,{"Carrier"})//remove " and "carrier=>"
in
#"Replaced Value1"
Regards,Xiaoxin Sheng
