Split Column function for time sheets

[Skitzy]

Hi Everyone, 

I am New to PowerBi. I have been tasked to create a dashboard for our staff timesheet information. All the info relating to projects and hours worked comes in 1 single cell in this format:

"[{"1":8,"2":8,"3":8,"4":8,"5":8,"Project":"MT-Admin","Total":"40.00","Title":"Solutions dev tasks","Category":"General"},{}]"

I want to know how to seperate the "Project", "Total", etc from this cell into seperate columns using the what i assume is correct to use, delimiter function. 

Any idea how to accomplish this? 

Thank you.

[amitchandak]

@Skitzy , Seems like json data. refer if this can help

https://www.mssqltips.com/sqlservertip/4621/using-power-bi-with-json-data-sources-and-files/

https://www.youtube.com/watch?v=ipI6mrWLQKA

[Skitzy]

You are correct, it is JSON data I should have mentioned that earlier, I will read through what you sent and report my results.

[parry2k · Accepted solution]

@Skitzy maybe read it as a json that's what it looks like. here is an example

start a blank query and click advanced editor and paste this code, and you can change it as per your need.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wiq6OUTKMUbKy0IlRMoLSxlDaBEqbQumAovys1OQSIC9GyTdE1zElNzMvRgkoEZJfkpgDFjYx0DMwgIhlluSkgsWC83NKSzLz84oVUlLLFEoSi7OLwSqcE0tS0/OLKsGK3FPzUotAhtTqVNfGKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Json.Document([Column1])),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Expanded Custom1" = Table.ExpandRecordColumn(#"Expanded Custom", "Custom", {"1", "2", "3", "4", "5", "Project", "Total", "Title", "Category"}, {"1", "2", "3", "4", "5", "Project", "Total", "Title", "Category"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"Column1"}),
    #"Removed Blank Rows" = Table.SelectRows(#"Removed Columns", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Skitzy]

@parry2k 

Hi again,

I want to include all the original data from the timesheet too, the code you sent me has removed all other columns of info such as the staff name etc. is there another advanced editor code to keep this data and just seperate the things i mentioned within the JSon cell?

[Skitzy]

@parry2k 

This is exactly what i needed!

I will have to add other staff members to what you sent me and also other fields but this is perfect. I will have to learn this for myself.