Forum Discussion
Solved
Help with transforming (cleaning?) data from an odd source
I have data that looks like this. There are categories off to the left but that is not an issue. These reports are out of a system that run from 6pm to 6pm. Sometimes, (Monday) the report will ...
yeah, that is a beast.
Your "24:00" made me chuckle. Haven't seen that in a looong time.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDBbsMgEER/ZeVeIwV2iUt6cxMnsUSM5OCqkuX//40EPLg1ygE9hmF3FqapomqXlrJ71tj/X/GcN2fzbqoeoQmd7186+NC4F1l9KRWpQQYFNAsVfAVfwVfJj71Pp5Hu/jw6T75viVIqcX1IJNILjUAfFwp8k30GLViv019c87hR21+H5qcd1gSLSkIlfy7UIBvonAhtFHxeJ4gpzt/ID99doA8duyJFowqayBYaaYKukjXusazvLVL4r4u2RUqu0vL+TVyDurhvU8p9dKG7uPZ3k8J1Mbvk6uP2p/JPZl9k+2PC1Tw/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]), #"Transposed Table" = Table.Transpose(Source), #"Removed Top Rows" = Table.Skip(#"Transposed Table",2), #"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Column1", type datetime}}), #"Filled Down" = Table.FillDown(#"Changed Type",{"Column1"}), #"Replaced Value" = Table.ReplaceValue(#"Filled Down",each [Column1],each Text.From(try [Column1] + Duration.FromText([Column2]) otherwise [Column1] + #duration(1,0,0,0)),Replacer.ReplaceValue,{"Column1"}), LZ = List.Zip({Table.ColumnNames(Source),List.FirstN(#"Transposed Table"[Column2],2) & #"Replaced Value"[Column1]}), #"Replaced Column Names" = Table.RenameColumns(Source,LZ), #"Removed Top Rows1" = Table.Skip(#"Replaced Column Names",2), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Top Rows1", {"STATION", "TOTAL"}, "Timestamp", "Value"), #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"TOTAL", Int64.Type}, {"Timestamp", type datetime}, {"Value", Int64.Type}}) in #"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
Here is an example
| 08/21 | 08/22 | |||||||||
| STATION | TOTAL | 20:00 | 21:00 | 22:00 | 23:00 | 24:00 | 01:00 | 02:00 | 03:00 | 04:00 |
| CCU MODULO ONE | 265 | 1 | 43 | 9 | 35 | 41 | 42 | 48 | 46 | |
| FLASH ENGRAVER | 281 | 2 | 27 | 17 | 24 | 19 | 34 | 40 | 22 | 41 |
| LOH ORBIT #1 | 214 | 8 | 7 | 30 | 37 | 38 | 23 | 35 | ||
| LOH ORBIT #2 | 184 | 23 | 13 | 17 | 24 | 26 | 21 | 23 | 18 | |
| MULTIFLEX #2 | 268 | 31 | 29 | 19 | 27 | 31 | 33 | 22 | 32 |
lbendlin
Super User
Super Useryeah, that is a beast.
Your "24:00" made me chuckle. Haven't seen that in a looong time.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZDBbsMgEER/ZeVeIwV2iUt6cxMnsUSM5OCqkuX//40EPLg1ygE9hmF3FqapomqXlrJ71tj/X/GcN2fzbqoeoQmd7186+NC4F1l9KRWpQQYFNAsVfAVfwVfJj71Pp5Hu/jw6T75viVIqcX1IJNILjUAfFwp8k30GLViv019c87hR21+H5qcd1gSLSkIlfy7UIBvonAhtFHxeJ4gpzt/ID99doA8duyJFowqayBYaaYKukjXusazvLVL4r4u2RUqu0vL+TVyDurhvU8p9dKG7uPZ3k8J1Mbvk6uP2p/JPZl9k+2PC1Tw/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t, Column10 = _t, Column11 = _t]),
#"Transposed Table" = Table.Transpose(Source),
#"Removed Top Rows" = Table.Skip(#"Transposed Table",2),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Top Rows",{{"Column1", type datetime}}),
#"Filled Down" = Table.FillDown(#"Changed Type",{"Column1"}),
#"Replaced Value" = Table.ReplaceValue(#"Filled Down",each [Column1],each Text.From(try [Column1] + Duration.FromText([Column2]) otherwise [Column1] + #duration(1,0,0,0)),Replacer.ReplaceValue,{"Column1"}),
LZ = List.Zip({Table.ColumnNames(Source),List.FirstN(#"Transposed Table"[Column2],2) & #"Replaced Value"[Column1]}),
#"Replaced Column Names" = Table.RenameColumns(Source,LZ),
#"Removed Top Rows1" = Table.Skip(#"Replaced Column Names",2),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Top Rows1", {"STATION", "TOTAL"}, "Timestamp", "Value"),
#"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"TOTAL", Int64.Type}, {"Timestamp", type datetime}, {"Value", Int64.Type}})
in
#"Changed Type1"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.