Combine and filter multiple Date/Time and data columns

[Syndicate_Admin]

This may or may not be compliated but I am new to power query and am still learning. I have a set of data that is a combination of 12 individual equiptment data columns, but each equiptment data has its own date/time column. The times do not always match and I cant just delete the other date/time columns. Also there is sometimes missing data or date/time values (It's one or the other missinbg not both). So I can't just append the tables together or else the date/time and correlating data may be wrong. How can I combine the columns into one and add another column that has the equipment line number. This may not be the best/optimized way to do it and am open to suggestions. 

I tried searching and could not find something I could use. I found this forum/question that was similar except my data is the same for each date/time data combo.

I also only need 2 data points for every day at ~8AM and ~8PM. I am not sure if I should make a seperate post for this issue but thought I would add it here.

This is a snippet of the data I am working with. I am using a filter right now to reduce the data points per day.

[Syndicate_Admin]

HI @NoahGrayhek ,

I updated the code a bit. The key functions are Unpivot (All the Date Columns), GroupBy, Demote Headers and Transpose. I did remove duplicate rows (You can delete this step if it doesn't suit your situation. I did it because I found a lot of repetitive data which should be grouped IMO),

Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

    //Replace missing data to 1/1/1900
    #"Replaced Value" = Table.ReplaceValue(
                            Source,
                            null,
                            #datetime(1900, 1, 1, 0, 0, 0),
                            Replacer.ReplaceValue,
                            List.Select(
                                Table.ColumnNames(Source), 
                                each 
                                    Text.Contains(_,"Time")
                            )
                        ),

    //
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(
                                #"Replaced Value", 
                                List.Select(
                                Table.ColumnNames(#"Replaced Value"), 
                                each 
                                    Text.Contains(_,"Value")
                            ), 
                                "TimeHdrs", 
                                "Date"
                            ),

    //Duplicate the date/time column
    #"Duplicated Column" = Table.DuplicateColumn(
                                #"Unpivoted Columns", 
                                "Date", 
                                "Time"
                            ),

    //Changed Type - Date and Time
    #"Changed Type" = Table.TransformColumnTypes(
                            #"Duplicated Column",
                            {
                                {"Date", type date}, 
                                {"Time", type time}
                            }
                        ),

    //Applied GroupBy - All Row to group Date and Time
    #"Grouped Rows" = Table.Group(
                            #"Changed Type", 
                            {"Date", "Time"}, 
                            {
                                {"Table", each _, type table}
                            }
                        ),

    //Expand all value columns (i.e. Part 1 Value, Part 2 Value ....
    #"Expanded Count" = Table.ExpandTableColumn(
                            #"Grouped Rows", 
                            "Table", 
                            List.Select(
                                Table.ColumnNames(#"Replaced Value"), 
                                each 
                                    Text.Contains(_,"Value")
                            )
                        ),

    //Removed replicate rows (This step can be removed)
    #"Removed Duplicates" = Table.Distinct(#"Expanded Count"),

    //Demote Headers before transposing to the desired view
    #"Demoted Headers" = Table.DemoteHeaders(#"Removed Duplicates"),

    //Transpose the table to the desired format
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),

    //Added Equipment# (This code needs to be updated with dynamic code. e.g. Equipment x. I need to understand how you pull the data, then I can update it.)
    AddedEquipmentx = #table({"Column1"}, {{"Equipment 1:"}}) & #"Transposed Table"
in
    AddedEquipmentx

Regards

KT