none
Group first three rows with the same index RRS feed

  • Question

  • Hello,

    There is a column "Type" with three options: "Count", "Bits" and "Volts", the three are always subsequent, in that order. I need to group the first three rows with the same index, starting with "Count", for example, rows 1 ("Count"), 2 ("Bits") and 3 ("Volts") are index "1"; rows 4 ("Count"), 5 ("Bits") and 6 ("Volts") are index "2" and so on.. How can I solve this?

    Please ask any question if not able to understand my explination.

    Thanks!



    • Edited by DaniloFhh Friday, April 3, 2020 1:29 PM
    Friday, April 3, 2020 1:29 PM

Answers

  • Hi

    Assuming Table1

    One way

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Type", type text}}),
        SplitTable = Table.FromList(
            Table.Split(ChangedType,3),
            Splitter.SplitByNothing(), {"NestedTables"}, null, ExtraValues.Error
        ),
        AddedIndex = Table.AddIndexColumn(SplitTable, "Index", 1, 1),
        ExpandedNestedTables = Table.ExpandTableColumn(AddedIndex, "NestedTables", {"Type"})
    in
        ExpandedNestedTables

    Friday, April 3, 2020 2:20 PM

All replies

  • Hi

    Assuming Table1

    One way

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(Source,{{"Type", type text}}),
        SplitTable = Table.FromList(
            Table.Split(ChangedType,3),
            Splitter.SplitByNothing(), {"NestedTables"}, null, ExtraValues.Error
        ),
        AddedIndex = Table.AddIndexColumn(SplitTable, "Index", 1, 1),
        ExpandedNestedTables = Table.ExpandTableColumn(AddedIndex, "NestedTables", {"Type"})
    in
        ExpandedNestedTables

    Friday, April 3, 2020 2:20 PM
  • That's exactly what I need, thanks!!
    Friday, April 3, 2020 2:30 PM
  • Great, so please Mark as answer the above proposal => Can help others - Thanks & nice day...
    Friday, April 3, 2020 2:39 PM