Generating a TimeGenerated column from the Source.Name

[Anonymous]

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!

[BA_Pete · Accepted solution]

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
    addTimeGenerated

Pete

[Anonymous]

Hi Pete,

Awesome, thank you so much for your help! You saved me a lot of headache! 

Tim