Help Transforming JSON Data

[Thomas_MedOne]

I have this table and it's formatted like this:

ID	wddate	wdhrs
100	2021-12-31	{"hr0":8,"hr1":8,"hr2":8,"hr3":7,"hr4":7,"hr5":7,"hr6":7,"hr7":35,"hr8":30,"hr9":30,"hr10":30,"hr11":32,"hr12":32,"hr13":34,"hr14":36,"hr15":37,"hr16":37,"hr17":35,"hr18":32,"hr19":10,"hr20":9,"hr21":9,"hr22":12,"hr23":10}

What is is for each hour of the day (midnight to 11pm) there is a number.

I need to sort of expand and "unpivot" (if that is the word) that so that it looks like:

id	wddate	hr	quantity
100	2021-12-31	0	8
100	2021-12-31	1	8
100	2021-12-31	2	8
etc	etc	etc	etc

This way I can report on it properly in PBI. I don't know how do to this. I do have it broken up with teh json into separate columns they look like ID, wddate, wdhrs.hr0, wdhrs.hr1, etc

But I need the "hour" to be in its own column and the value then in there too. I don't know how to make that happen. Is it even possible?

[KT_Bsmart2gethe · Accepted solution]

Hi @Thomas_MedOne ,

below is the code:

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Tc0xDoBACETRu1BrwoCuq1dRe2tb491dlgTpHsmEv+8EZhpIWDBCRkU7noOumw/a6mBCSELatHRNoTlUQkuTzp3VyJ3rT3CylVTckmw1ndzW0+K2onoIJTlVUdMf68JbYt3ViZ9Wha9F+/ql8/wA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, wddate = _t, wdhrs = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"wddate", type date}, {"wdhrs", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","{","",Replacer.ReplaceText,{"wdhrs"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","}","",Replacer.ReplaceText,{"wdhrs"}),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Replaced Value1", {{"wdhrs", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "wdhrs"),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "wdhrs", Splitter.SplitTextByDelimiter(":", QuoteStyle.None), {"wdhrs.1", "wdhrs.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"wdhrs.1", type text}, {"wdhrs.2", Int64.Type}}),
#"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "wdhrs.1", Splitter.SplitTextByPositions({0, 2}, false), {"wdhrs.1.1", "wdhrs.1.2"}),
#"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"wdhrs.1.1", type text}, {"wdhrs.1.2", Int64.Type}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"wdhrs.1.2", "Hr"}, {"wdhrs.2", "quantity"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"wdhrs.1.1"})
in
#"Removed Columns"

Regards

KT

[Thomas_MedOne]

I got this one to work, thanks for your help!

[Thomas_MedOne]

In this situation, if the source is an SQL table, would I start at the first "Change Type" row?

[KT_Bsmart2gethe]

I assumed it is still 3 columns but without "{" & "}" from SQL. So, after the Replace Value1.

[artemus]

You can add a custom column like:

= let json = Json.Document(_) in #table(type table [Hour=text, Work=number], List.Zip({List.Transform(Record.FieldNames(json), each Text.AfterDelimiter(_, "hr")), Record.ToList(json)}))

Then just expand the table column, and convert the columns to numbers.

[Thomas_MedOne]

Would this be after the import of the table? The fields are in a database.