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 var...
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

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"
      • Anonymous's avatar
        Anonymous
        Not applicable
        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