Good Power Query, part 1

20 Jan, 2022

Good Power Query!

Today was a really satisfying day at work, hasing out some Power Query. I feel really accomplished, but I feel like it would be too much to break down what I actually did. Besides, there are actually two of them that I want keep on the blog for today, so this will be a Power Query anthology. I deliberately called this part 1 because I anticipate there will be more in the future.

Things I'm proud of

I put in a function as an argument into another function

    aggregateSectionColumn = (value as any, columnName as text, aggregateFunction as function) =>

I looped through a whole bunch of table columns using List.Transform() and Table.Column.
Using types liberally. I've been writing a lot of TypeScript recently, and using type declarations on variables and return types is so handy. It really helps with the way I "see" my code. When there's a type error in compilation, it really helps with debugging. It's not like Excel/Power BI will give you better error messages (they will still be almost unhelpfully terse) but because I was the one who explicitly wrote some of those type declarations, I remember where those objects are a bit better. Also, it really helps out my future self when I explicitly write whether a function is returning e.g. a list, or a table.
I had to chain together a whole lot of OR statements. Instead of writing out each and every clause, I looped through all of the conditions I needed to check. I then summed (List.Sum) all of them. If the result was greater than one, then give true - otherwise, give false. This was a trick that I used to use in Power Apps, and I'm glad that it works equally well here.

Full code

The first one

let
    // only needs to be a part of the name, not the full name
    name = "Somebody Name",

    checkNames = (session as record) => 
        let 
            // this function uses a plus + operator to act as a "OR". 
            // positive means the name is in there
            presenceInBinary = List.Transform(
                // for each column in nameTypes...
                nameTypes_sessions, // external module
                each 
                    Number.From(
                        Text.Contains(Record.Field(session, _),name)
                    )   
            ),
            comparison = List.Sum(presenceInBinary) > 0
        in 
            comparison,

    thisPersonOnly = Table.SelectRows(
        sessions,
        each 
            // go through all the Persons columns and see if the person is in there
            checkNames(_)
    ),

    selectColumns = Table.SelectColumns(
        thisPersonOnly, 
        List.Combine( { { "ID", "ActID", "Facility", "Facility_1", "Due Date", "Activity", "Status" }, nameTypes_sessions } )
    ),
    
    getPerson = (listOfNames as text) => 
        let 
            textSplit = Text.Split(
                Text.Replace(listOfNames, ",", ";"),
                ";"
            ),
            filterName = List.Select(
                textSplit, 
                each Text.Contains(_, name)
            )
        in 
            Text.Combine(filterName),
    
    transformations = List.Transform(
        nameTypes_sessions, 
        each { _, each getPerson(_) }
    ),
    
    split = Table.TransformColumns(
        selectColumns, 
        transformations
    )


in
    split

The second one

let

    // pull in the folder path and filename from paths file
    whichPath = 0,
    folderPath = getFolderPath(whichPath)[folderPath],
    filename = getFolderPath(whichPath)[filename],

    // pull in the project plan file
    home = root & "/sites/Operations_Support__All",
    Source = SharePoint.Files(home, [ApiVersion = 15]),
    #"Filtered Rows" = Table.SelectRows(
        Source, 
        each 
            ([Extension] = ".xlsx")
            and ([Folder Path] = home & folderPath)
    ),
    fileBinary = Source{[Name=filename]}[Content],

    excel = Excel.Workbook(fileBinary),
    excelPlanTable = excel{[Item="Schedule",Kind="Sheet"]}[Data],
    
    // transform table
    topAndBottomRemoved = removeTopAndBottom(excelPlanTable),

    // isolate all of the group heading
    groupsWithIndex = getGroupsWithIndex(topAndBottomRemoved),

    // continue processing table
    fillDownIndeces = Table.Buffer(addGroupIndeces(topAndBottomRemoved, groupsWithIndex)),

    // get earliest and latest
    sections = List.Transform(
        groupsWithIndex[Index], 
        each turnIntoSectionWithGroupDates(_, fillDownIndeces)
    ),

    final = cleanUp(sections, filename)

in
    final

// setEarliestAndLatestGroupDates
(filteredByGroup as table, index as number) as table => 
    let 
        listFromColumBySection = (columnName as text) as list => 
            Table.Column(
                Table.SelectRows(
                    filteredByGroup as table,
                    each [Index] = index
                ) as table,
                columnName as text
            ) as list,

        aggregateSectionColumn = (value as any, columnName as text, aggregateFunction as function) => 
            if value = null then 
                let
                    sectionColumn = listFromColumBySection(columnName) as list,
                    aggregate = aggregateFunction(sectionColumn)
                in
                    aggregate
            else value
        ,

        final = Table.TransformColumns(
            filteredByGroup,
            {
                {
                    "Stakeholders", 
                    each 
                        aggregateSectionColumn(_, "Stakeholders", (sectionColumn as list) => 
                            Text.Combine(
                                List.Distinct(sectionColumn) as list,
                                ";"
                            ) as text
                        )
                },
                {
                    "ASSIGNED#(lf)TO", 
                    each 
                        aggregateSectionColumn(_, "ASSIGNED#(lf)TO", (sectionColumn as list) => 
                            Text.Combine(
                                List.Distinct(sectionColumn) as list,
                                ";"
                            ) as text
                        )
                },
                {
                    "PROGRESS", 
                    each 
                        aggregateSectionColumn(_, "PROGRESS", (sectionColumn as list) => 
                            List.Average(sectionColumn) as number
                        )
                },
                {
                    "START", 
                    each 
                        aggregateSectionColumn(_, "START", (sectionColumn as list) => 
                            List.Min(sectionColumn) as date
                        )
                },
                {
                    "END", 
                    each 
                        aggregateSectionColumn(_, "END", (sectionColumn as list) => 
                            List.Max(sectionColumn) as date
                        )
                }
            }
        )
    in
        final

#excel #powerquery