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"
Pot2000
2 years agoRegular Visitor
Some extra info i use the following formulas in excel. And than copy past values and make numbers from text.
=Iferror(Left(E3;search("d";E3)-1);0)
=iferror(iferror(mid(E3;search("u";E3)-2;2);mid(E3;search("u";E3)-1;1));0)
=iferror(mid(E3;search("m";E3)-2;2);0)
I than load this file into POwerBI.
Howeever i would like to do this in Powerbi since it is someting that comes back every quater with 3 differant dumps.