Repeat a function using a table for the variable inputs.

[MarkusEng1998]

I have an Excel spreadsheet formatted like a Word table.
The spreadsheet has about 20 "Word tables", so I created a function to extract the data for each table.

         e.g. fxGetRoomData (NamedRange, RoomSection, startingIndex)

I create a separate query for each of the 20 tables using this fxGetRoomData function.

Lastly, I append all of these queries into a single query.

Instead of creating separate queries for each table,

I would like one query where the fxGetRoomData function reads a separate RoomTable for the three variables.
The fxGetRoomData iterates through each row of the RoomTable.

Please advise whether this is possible.

Thank your for any suggestions.

[MarkusEng1998]

This is the error when I create a custom column applying the fxGetRooms functions.

Thank you again, for your initial suggestion.

[MarkusEng1998]

thank you @KT_Bsmart2gethe for your link. It was helpful. Here is my function.

The function filters out all of the rows that do not have calculated room areas.

let
    Source = (FunctionRange, FunctionColumn, ndx, Opt) => let
        Source = Excel.CurrentWorkbook(){[Name=FunctionRange]}[Content],
        promoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
        changeType = Table.TransformColumnTypes(promoteHeaders,{{"Column1", type text}, {FunctionColumn, type text}, {"Column3", type text}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type number}, {"Column11", type number}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", Int64.Type}}),
        removeColumns = Table.SelectColumns(changeType,{"Column1", FunctionColumn, "Column3", "Column10", "Column11"}),
        renameColumns = Table.RenameColumns(removeColumns,{{"Column1", "Code"}, {"Column3", "Prog"}, {"Column10", "MinArea"}, {"Column11", "TotalArea"}}),
        filterNull = Table.SelectRows(renameColumns, each ([TotalArea] <> 0) and ([TotalArea] <> null) and ([Code] <> null)),
        renameColumns2 = Table.RenameColumns(filterNull,{{FunctionColumn, "Room"}}),
        addFunction = Table.AddColumn(renameColumns2, "Function", each FunctionColumn),
        addProgram = Table.AddColumn(addFunction, "Program", each Text.Start([Prog], Text.PositionOf([Prog], ":"))),
         #"Removed Columns" = Table.RemoveColumns(addProgram,{"Prog"}),
        reorderColumn = Table.ReorderColumns(#"Removed Columns",{"Code", "Function", "Room", "Program", "MinArea", "TotalArea"}),
        replaceNullMinArea = Table.ReplaceValue(reorderColumn,null,"",Replacer.ReplaceValue,{"MinArea"}),
        addNDX = Table.AddIndexColumn(replaceNullMinArea, "Index", ndx, 1, Int64.Type),
        changeNDX = Table.TransformColumnTypes(addNDX,{{"Index", type text}}),
        addSortNDX = Table.AddColumn(changeNDX, "sortNDX", each if Text.Length(Number.ToText(ndx))= 3 then Opt & "0" & [Index] else Opt & [Index]),
        removeIndex = Table.RemoveColumns(addSortNDX,{"Index"}),
        renameSortNDX = Table.RenameColumns(removeIndex,{{"sortNDX", "Index"}})
in
    renameSortNDX
in
    Source

The green boxes are some of the parameters for the function.

There are about 20 rooms that use this function.

I have the fxGetRoomNotes function that separates the title and notes (row 10-12) and inserts a blank row.

The ndx (index) parameter allows me to have the room notes go before the room areas.

The final output is the AllRooms query, which appends each of the 20 room areas with their notes...

[KT_Bsmart2gethe]

Hi @MarkusEng1998 ,

In your function, "Source = (FunctionRange, FunctionColumn, ndx, Opt) =>", FunctionColumn has to be a column, which is not in this case. It is very likely that " promoteHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," did promote the headers as per your expectation. You can run a simple test by testing the first two steps to visualise the data. If you don't get to see "Exterior Storage" then you will need to update the code ensuring you have what you want on the header row.

Regards

KT

[KT_Bsmart2gethe · Accepted solution]

Hi @MarkusEng1998 ,

Without looking at your function code, it is not straightforward as it depends on how you write the code.

Feel free to watch to the video below to get the idea. You can fast forward to 3.55 and see how it can be done in your case.

https://youtu.be/K9IB9bLxEXo

Regards

KT