Help with transforming (cleaning?) data from an odd source

[finglonger76]

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 have 6pm Friday through 6pm Sunday. In the example, you can see there is a header for the date, but the times are underneath. 

How can I get the date copied down into the time field dynamically? I want to have a folder with the daily report and it be able to get the date and attach it to the hours if it is either a one day report or two day report. 

To be simple, in the example: I want the date 8/21 to be copied into hours 20,21,22,23,24 then 8/22 copied into hours 01,02,03,04 etc.

[lbendlin]

Remember that the first row can be addressed as {0}, the second row as {1}  etc. You can use Power Query to combine values from cells in different rows.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[finglonger76]

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 · Accepted solution]

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.

[finglonger76]

Thanks, I have to wiggle the code a little but that did the trick. 

[lbendlin]

Note that I mapped the "24:00"  to 00:00 of the next day.