Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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
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
I got this one to work, thanks for your help!
In this situation, if the source is an SQL table, would I start at the first "Change Type" row?
I assumed it is still 3 columns but without "{" & "}" from SQL. So, after the Replace Value1.
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.
Would this be after the import of the table? The fields are in a database.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
53 | |
21 | |
13 | |
11 |