Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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" |
Solved! Go to Solution.
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
Hi @alromeyn,
Does Power BI gives you error while you uploading the hstore values from postgres ?
I am also trying to get the values from my DB into power BI and getting error for hstore value. Even I tried converting it into text field too.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
75 | |
52 |
User | Count |
---|---|
144 | |
109 | |
108 | |
88 | |
61 |