Forum Discussion
Generating a TimeGenerated column from the Source.Name
Hello all,
I'm fairly new to powerBi and I couldn't figure out how to get this working in power Query.
I have a dataset like this:
| Source.Name | DeviceCount |
| Devices20230508.json | 215 |
| Devices20230509.json | 213 |
| Devices20230510.json | 218 |
| Devices20230511.json | 221 |
As you can see, the Source.Name is time stamped (8th of may, 9th of may, etc.) I want to generate a time column based on the content of the Source.Name column. Below would be my desired result:
| Source.Name | DeviceCount | Time Generated |
| Devices20230508.json | 215 | 2023-05-08 |
| Devices20230509.json | 213 | 2023-05-09 |
| Devices20230510.json | 218 | 2023-05-10 |
| Devices20230511.json | 221 | 2023-05-11 |
Any help would be greatly appreciated. Thanks!
Hi Anonymous ,
Add this as a new custom column:
let numbers = Text.Select([Source.Name], {"0".."9"}) in #date( Number.From(Text.Start(numbers, 4)), Number.From(Text.Middle(numbers, 4, 2)), Number.From(Text.End(numbers, 2)) )For this output:
Full example query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wckkty0xOLTYyMDI2MDWw0Msqzs9TitVBljAyNDIyhUrEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t]), addTimeGenerated = Table.AddColumn( Source, "Time Generated", each let numbers = Text.Select([Source.Name], {"0".."9"}) in #date( Number.From(Text.Start(numbers, 4)), Number.From(Text.Middle(numbers, 4, 2)), Number.From(Text.End(numbers, 2)) ) ) in addTimeGeneratedPete
2 Replies
Hi Anonymous ,
Add this as a new custom column:
let numbers = Text.Select([Source.Name], {"0".."9"}) in #date( Number.From(Text.Start(numbers, 4)), Number.From(Text.Middle(numbers, 4, 2)), Number.From(Text.End(numbers, 2)) )For this output:
Full example query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wckkty0xOLTYyMDI2MDWw0Msqzs9TitVBljAyNDIyhUrEAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Source.Name = _t]), addTimeGenerated = Table.AddColumn( Source, "Time Generated", each let numbers = Text.Select([Source.Name], {"0".."9"}) in #date( Number.From(Text.Start(numbers, 4)), Number.From(Text.Middle(numbers, 4, 2)), Number.From(Text.End(numbers, 2)) ) ) in addTimeGeneratedPete