none
Create Table from the values on certain columns, prep hours for MS Project RRS feed

  • Question

  • I have a table that lists teams, # of weeks required in a phase of a project, and the number of hours each team will work per week in those phases (Left). My goal, using power query, is to turn that into a table that lists the team as column 1, and then columns in sequential order representing weeks from the phases, with the hours for those weeks as the values.

    Sure... easy enough to do by hand. This is just an example. The number of phases and weeks in the phases greatly vary. My goal is to make it so I can just copy and paste into MS project.


    Ian


    • Edited by Ian Bruckner Saturday, November 11, 2017 10:58 PM
    Saturday, November 11, 2017 10:39 PM

Answers

  • Please try this function:

    (Table as table) =>
    let
        Source0=Table,
        ColumnNames = Table.ColumnNames(Source0),
        NumberOfIntervals = (List.Count(ColumnNames)-1)/2,
        Weeks = List.Range(ColumnNames,1,NumberOfIntervals),
        ListOfWeeks = Record.FieldValues(Table.SelectColumns(Source0, Weeks){0}),
        Repeats = List.Transform(ListOfWeeks, each {1.._}),
        ValueColumns = Table.SelectColumns(Source0,List.Range(ColumnNames, 1+NumberOfIntervals, NumberOfIntervals)),
        ListOfValueColumns = Table.ToColumns(ValueColumns),
        ZipWithValues = List.Zip({Repeats,ListOfValueColumns}),
        CloneColumns = List.Combine(List.Transform(ZipWithValues, (outer) => List.Transform(outer{0}, each outer{1}))),
        Reassemble = Table.FromColumns(List.Combine({{Table.Column(Source0, ColumnNames{0})},CloneColumns}), List.Combine({{ColumnNames{0}},List.Transform({1..List.Sum(ListOfWeeks)}, each "W"&Text.From(_))}))
    in
        Reassemble

    Just reference your source table in the parameters.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by Ian Bruckner Monday, November 13, 2017 2:14 AM
    • Unmarked as answer by Ian Bruckner Monday, November 13, 2017 2:14 AM
    • Marked as answer by Ian Bruckner Monday, November 13, 2017 3:22 AM
    Sunday, November 12, 2017 9:35 PM
    Moderator

All replies

  • How about posting a link to some sample data

    Self Service BI Expert using Power Pivot http://exceleratorbi.com.au

    Sunday, November 12, 2017 1:46 AM
  • Not sure how tables work out in technet, but here's a shot... from that I'd do New Query From Table.

    TeamName WksInPhase1 WksInPhase2 WksInPhase3 Hrs/wk_phase1 Hrs/wk_phase2 Hrs/wk_phase3
    TeamA 1 2 1 4 2 8
    TeamB 1 2 1 5 3 8
    TeamC 1 2 1 6 4 8
    TeamD 1 2 1 7 5 8
    TeamE 1 2 1 8 6 8
    TeamF 1 2 1 9 7 8


    Ian


    Sunday, November 12, 2017 1:57 AM
  • Please try this function:

    (Table as table) =>
    let
        Source0=Table,
        ColumnNames = Table.ColumnNames(Source0),
        NumberOfIntervals = (List.Count(ColumnNames)-1)/2,
        Weeks = List.Range(ColumnNames,1,NumberOfIntervals),
        ListOfWeeks = Record.FieldValues(Table.SelectColumns(Source0, Weeks){0}),
        Repeats = List.Transform(ListOfWeeks, each {1.._}),
        ValueColumns = Table.SelectColumns(Source0,List.Range(ColumnNames, 1+NumberOfIntervals, NumberOfIntervals)),
        ListOfValueColumns = Table.ToColumns(ValueColumns),
        ZipWithValues = List.Zip({Repeats,ListOfValueColumns}),
        CloneColumns = List.Combine(List.Transform(ZipWithValues, (outer) => List.Transform(outer{0}, each outer{1}))),
        Reassemble = Table.FromColumns(List.Combine({{Table.Column(Source0, ColumnNames{0})},CloneColumns}), List.Combine({{ColumnNames{0}},List.Transform({1..List.Sum(ListOfWeeks)}, each "W"&Text.From(_))}))
    in
        Reassemble

    Just reference your source table in the parameters.


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    • Marked as answer by Ian Bruckner Monday, November 13, 2017 2:14 AM
    • Unmarked as answer by Ian Bruckner Monday, November 13, 2017 2:14 AM
    • Marked as answer by Ian Bruckner Monday, November 13, 2017 3:22 AM
    Sunday, November 12, 2017 9:35 PM
    Moderator
  • That's b e a utiful! Later this week I'll post the whole thing - could prove useful to others who might do some high level resource estimates and want to plot out a probable schedule and be able to just copy and paste into Ms Project and let that feed a portfolio analysis in Project Online to determine plausible start dates.


    Ian

    Monday, November 13, 2017 2:14 AM
  • Great to hear.

    You might have noticed already that this function just works by the position of the columns: The first column contains the ID of the rows. Then comes a number of columns indicating the column repetition, followed by the same number of columns containing the data (to be repeated).


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Monday, November 13, 2017 6:59 AM
    Moderator
  • This is a little later than a week ;)... but here's the final product for anyone else who'd like to see:

    High Level Schedule Creation


    Ian

    Monday, December 11, 2017 8:10 PM