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. As you can see the times and days do not always match up on rows. I am using a filter right now to reduce the data points per day.
HI @Anonymous,
Is below outcome what you're looking for?
I need to re-write part of the code to make it dynamic if this is what you are after.
Code:
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
//Only keep two Date/Time Columns
KeepDateTime = Table.RemoveColumns(
Source,
List.RemoveFirstN(
List.Select(
Table.ColumnNames(Source),
each
Text.Contains(_,"Time")
),
2
)
),
//Renamed the two Date/Time columns to Date & Time
RenamedDateTime = Table.RenameColumns(
KeepDateTime,
List.Transform(
List.Select(
Table.ColumnNames(KeepDateTime),
each
Text.Contains(_,"Time")
),
each
{_, if Text.StartsWith(_,"Part 2")
then "Time"
else "Date"
}
)
),
//Reordered columns to Date, Time, Part 1, 2 3 ....
ReorderedCol = Table.ReorderColumns(
RenamedDateTime,
{"Date", "Time"} & List.Select(
Table.ColumnNames(RenamedDateTime),
each
_<>"Time" and _<>"Date"
)
),
//Changed Data Type Date & Time
#"Changed Type" = Table.TransformColumnTypes(
ReorderedCol,
{
{"Date", type date},
{"Time", type time}
}
),
//Demote headers before transpose
DemotedHdrs = Table.DemoteHeaders(#"Changed Type"),
//Transpose Table
TransposedTbl = Table.Transpose(DemotedHdrs),
//Add label - "Equipment x" (required to rewrite this part to make it dynamics label)
CombineTbls = Table.Combine({#table({"Column1"},{{"Equipment 1:"}}),TransposedTbl})
in
CombineTbls
Regards
KT
Hi @Anonymous
I just went through the original post. Somehow, I missed it.
I am not sure if I fully understood the date logic. For example, what should we do if there is a missing date or unmatch date? I need to understand to be able to write the code.
I was wondering if grouping the date is an option.
I am sorry for asking too many questions, as I am clear about the outcome
Regards
KT
HI @Anonymous ,
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
Helpful resources
Fabric Data Days
Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!
Power BI Monthly Update - October 2025
Check out the October 2025 Power BI update to learn about new features.
FabCon Atlanta 2026
Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
