Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Combine and filter multiple Date/Time and data columns

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 ...
Need Help
Anonymous's avatar
Anonymous
Not applicable
4 years ago

So I went through your code and I see it just deletes the dates of the other columns but it can't just do that. It needs to check and make sure the dates and times match or are at least close to each other. The screen shot in the original post showed the need for it. Some can have blank data or not recorded anything for a full day.

 

I am not versed I. DAX but would it be best to to write code to check if the times match and if not then match it to the correct date+time? 

KT_Bsmart2gethe's avatar
KT_Bsmart2gethe
Impactful Individual
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    So if the dates do not match they should be moved/grouped with the proper time so they match. If the data is missing for a certain time then it should just be left blank or 0. The tolerance is not firm. So if the time is 5 seconds off then it can be grouped together. Maybe it can be run through a filter so that the average, max, and min of 5 seconds is returned. There is a few ways that each one could be needed (sometimes need the max, other times the average is needed. This would be changed later depending on the particular need). This issue is a common one that prevents moving forward on a few different things.

     

    If times match then its good if not then put 0 in value for that part.

    • KT_Bsmart2gethe's avatar
      KT_Bsmart2gethe
      Impactful Individual
      4 years ago

      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