none
transform 9 element list of numbers into a 3x3 table RRS feed

  • Question

  • How can I transform a list of numeric data 

    1

    2

    3

    4

    5

    6

    7

    8

    9

    into a table that looks like

    1 2 3 

    4 5 6

    7 8 9

    in power query

    Thanks

    Thursday, February 8, 2018 5:28 PM

Answers

  • Here's one possibility.

    let
        Source = {1..9},
        GroupNum = 3,
        GroupStartIndexes = List.Transform({0..Number.RoundUp((List.Count(Source)/GroupNum)) - 1}, each _ * GroupNum),
        GroupedLists = List.Accumulate(GroupStartIndexes, {}, (state, current) => state & {List.Range(Source, current, GroupNum)}),
        FilledNulls = List.Zip(List.Zip(GroupedLists)), //for Source lists not evenly divisible by GroupNum
        ConvertedToTable = Table.FromRows(FilledNulls) 
    in
        ConvertedToTable

    • Marked as answer by chathame Friday, February 9, 2018 1:00 AM
    Thursday, February 8, 2018 9:31 PM

All replies

  • Here's one possibility.

    let
        Source = {1..9},
        GroupNum = 3,
        GroupStartIndexes = List.Transform({0..Number.RoundUp((List.Count(Source)/GroupNum)) - 1}, each _ * GroupNum),
        GroupedLists = List.Accumulate(GroupStartIndexes, {}, (state, current) => state & {List.Range(Source, current, GroupNum)}),
        FilledNulls = List.Zip(List.Zip(GroupedLists)), //for Source lists not evenly divisible by GroupNum
        ConvertedToTable = Table.FromRows(FilledNulls) 
    in
        ConvertedToTable

    • Marked as answer by chathame Friday, February 9, 2018 1:00 AM
    Thursday, February 8, 2018 9:31 PM

  • Excellent, this worked, thank you very much

    Friday, February 9, 2018 1:00 AM