Forum Discussion
Solved
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 to split the data as below
| Days | hours | min |
| 30 | 22 | 3 |
| 0 | 1 | 7 |
| 0 | 0 | 57 |
Any help is welkom
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"
3 Replies
- Ahmedx
Super User
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"Works like a charm thank you
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.
