Forum Discussion

cricks's avatar
cricks
Frequent Visitor
6 years ago
Solved

Multiple date/time columns

I'm trying to work with Excel data that is grouped by date/time. There can be multiple groups, each with their own DateTime column. Each group will have a different start time and interval, and a variable number of data fields. Here is an illustration of what the Excel sheets can look like. I want to combine and transpose all the groups of data to be in a single query with the format DateTime - Attribute - Value. I can split up the groups, unpivot, and recombine manually, but if the number of groups or the number of data columns in each group is different, I have to create a custom query.

Is it possible to make a query that is flexible enough to handle different size or number of data groups?

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi all,

     

    I did it slightly differently:

    let
    Source = myTable,
    Columns =  List.Buffer(List.Accumulate(Table.ColumnNames(Source), {}, (s, a)=> if Text.StartsWith(a,"DateTime") then s & {{a}} else List.RemoveLastN(s,1) & {List.Last(s) & {a}} )),
    Output = Table.Combine(List.Accumulate(Columns, {}, (s, a) => s & {Table.UnpivotOtherColumns(Table.RenameColumns(Table.SelectColumns(Source, a), {a{0}, "DateTime"}) , {"DateTime"}, "Attribute", "Value")}))
in
    Output

    The code above first split the columns into groups and then unpivot and combine group-by-group.

    Column groups list is buffered, but this can be removed the number of columns is quite large.

     

    Kind regards,

    John

9 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi cricks 

     

    Can you provide a workable data sample and expected outcome?  ( You can paste a copy of an excel table in the body of the comment section )

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    Mariusz Repczynski

     

    • cricks's avatar
      cricks
      Frequent Visitor
      6 years ago

      Here's the input format. The start time, interval, and end time for each group of data can be different. 

      I've also uploaded the Excel file here. 

       

      DateTimeData ADateTimeData BData CDateTimeData EData FData GData HData IDateTimeData J
      08/26/19 09:17:3946.5408/26/19 09:39:215157.468/26/19 0:00:007980.52133.833.0714759.60372544.588/24/18 0:00:0018.58
      08/26/19 09:19:3946.708/26/19 09:40:2151.157.268/26/19 0:05:008308.82252.973.2393959.81672659.078/24/18 0:01:0022.61
      08/26/19 09:21:3946.7408/26/19 09:41:2156.4657.78/26/19 0:10:008456.72226.573.2064559.86872654.938/24/18 0:02:0014.89
      08/26/19 09:23:3947.2408/26/19 09:42:2151.0657.588/26/19 0:15:0084292188.733.1532960.1292597.158/24/18 0:03:007.16
      08/26/19 09:25:3946.8208/26/19 09:43:2149.0657.768/26/19 0:20:008317.72134.323.0808560.04082549.028/24/18 0:04:0010.20
      08/26/19 09:27:3947.1408/26/19 09:44:2152.9857.528/26/19 0:25:008458.72207.583.178959.88752622.758/24/18 0:05:0012.47
      08/26/19 09:29:3946.9608/26/19 09:45:2151.6657.068/26/19 0:30:008252.42130.673.0721859.942535.778/24/18 0:06:0014.74

       

       

      Desired output format:

       

      DateTimeAttributeValue
      08/26/19 09:17:39Data A46.54
      08/26/19 09:19:39Data A46.7
      08/26/19 09:21:39Data A46.74
      08/26/19 09:23:39Data A47.24
      08/26/19 09:25:39Data A46.82
      08/26/19 09:27:39Data A47.14
      08/26/19 09:29:39Data A46.96
      08/26/19 09:39:21Data B51
      08/26/19 09:40:21Data B51.1
      08/26/19 09:41:21Data B56.46
      08/26/19 09:42:21Data B51.06
      08/26/19 09:43:21Data B49.06
      08/26/19 09:44:21Data B52.98
      08/26/19 09:45:21Data B51.66
      08/26/19 09:39:21Data C57.46
      08/26/19 09:40:21Data C57.26
      08/26/19 09:41:21Data C57.7
      08/26/19 09:42:21Data C57.58
      08/26/19 09:43:21Data C57.76
      08/26/19 09:44:21Data C57.52
      08/26/19 09:45:21Data C57.06
      8/26/19 0:00:00Data E7980.5
      8/26/19 0:05:00Data E8308.8
      8/26/19 0:10:00Data E8456.7
      8/26/19 0:15:00Data E8429
      8/26/19 0:20:00Data E8317.7

       

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi cricks 

    It may be possible.

    We need to know that:

    is the screenshot the original data in excel or expected format after transforming with Power BI?

    is there "date" column in your original data? 

    are the "date" for each group same?

     

    Best Regards
    Maggie

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi cricks 

    In Edit queries, create a new blank query, enter code in Advanced editor, you could also select each step from "applied steps" pane to see details of each step.

    let
    Source1 = Table,
    #"Removed Other Columns1" = Table.SelectColumns(Source1,{"DateTime 1", "Data A"}),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Removed Other Columns1", {"DateTime 1"}, "Attribute", "Value"),
    Group1= Table.RenameColumns(#"Unpivoted Columns1",{{"DateTime 1", "DateTime"}}),

    Source2 = Table,
    #"Removed Other Columns2" = Table.SelectColumns(Source2,{"DateTime2", "Data B", "Data C"}),
    #"Unpivoted Columns2" = Table.UnpivotOtherColumns(#"Removed Other Columns2", {"DateTime2"}, "Attribute", "Value"),
    Group2= Table.RenameColumns(#"Unpivoted Columns2",{{"DateTime2", "DateTime"}}),

    Source3 = Table,
    #"Removed Other Columns3" = Table.SelectColumns(Source3,{"DateTime3", "Data E", "Data F", "Data G", "Data H", "Data I"}),
    #"Unpivoted Columns3" = Table.UnpivotOtherColumns(#"Removed Other Columns3", {"DateTime3"}, "Attribute", "Value"),
    Group3= Table.RenameColumns(#"Unpivoted Columns3",{{"DateTime3", "DateTime"}}),

    Source4 = Table,
    #"Removed Other Columns4" = Table.SelectColumns(Source4,{"DateTime4", "Data J"}),
    #"Unpivoted Columns4" = Table.UnpivotOtherColumns(#"Removed Other Columns4", {"DateTime4"}, "Attribute", "Value"),
    Group4= Table.RenameColumns(#"Unpivoted Columns4",{{"DateTime4", "DateTime"}}),

    Source_final= Table.Combine({Group1, Group2, Group3, Group4})
in
    Source_final
    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    • cricks's avatar
      cricks
      Frequent Visitor
      6 years ago

      The problem is that the number of groups may vary, and the number of data fields may vary, and the names of the data fields may vary. The only constant is that the first column in each group will always be named "DateTime".

       

      I'm looking for a way to reliably identify and separate the groups of data, even if the number of groups changes, the number of data fields in each group changes, or the names of the data fields change. With v-juanli-msft's solution, the query will break if any of these are true. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Does this query work for you?

         

        let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    NameChanges = List.Accumulate(Table.ColumnNames(Source), {}, (s,c) => s & {if Text.StartsWith(c,"DateTime") then c   else Text.Start(List.Last(s),9) & "." & c}),
    RenameSource = Table.RenameColumns(Source, List.Zip({Table.ColumnNames(Source) , NameChanges})),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(RenameSource, {}, "Attribute", "Value"),
    Transform = Table.TransformColumns(#"Unpivoted Other Columns",{{"Attribute", each Text.AfterDelimiter(_,"."), type text}}),
    AddDateTime = Table.AddColumn(Transform, "DateTime", each if [Attribute]= "" then [Value] else null, type datetime),
    #"Filled Down" = Table.FillDown(AddDateTime,{"DateTime"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([Attribute] <> ""))
in
    #"Filtered Rows"