Forum Discussion
Pot2000
2 years agoRegular Visitor
Split colum when value is missing the null value should be in the first colum
I have a data dump where one of the colums contains a period of time that i would like to split in day's hours and minutes. The data is as follows. 30d 22h 3m 1h 17m 57m I would like t...
- 2 years ago
pls try code
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZIUTAyylAwzlWK1YlWMsxQMDSHME1BdCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [ a = Text.Split([Data]," "), b= List.Transform(a,(x)=> Splitter.SplitTextByCharacterTransition({"0".."9"},(x)=>not List.Contains({"0".."9"},x))(x)), c= Table.FromList(b,(x)=>x), e= Table.TransformColumns(c,{"Column2",(x)=> if x = "d" then "Days" else if x= "h" then "hours" else "min"}) ][e]), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}), #"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Column2]), "Column2", "Column1") in #"Pivoted Column"
Ahmedx
Super User
2 years agopls try code
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZIUTAyylAwzlWK1YlWMsxQMDSHME1BdCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Data", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each [
a = Text.Split([Data]," "),
b= List.Transform(a,(x)=> Splitter.SplitTextByCharacterTransition({"0".."9"},(x)=>not List.Contains({"0".."9"},x))(x)),
c= Table.FromList(b,(x)=>x),
e= Table.TransformColumns(c,{"Column2",(x)=> if x =
"d" then "Days" else if x= "h" then "hours" else "min"}) ][e]),
#"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Pivoted Column" = Table.Pivot(#"Expanded Custom", List.Distinct(#"Expanded Custom"[Column2]), "Column2", "Column1")
in
#"Pivoted Column"Pot2000
2 years agoRegular Visitor
Works like a charm thank you