PowerBI assistance nedded - Loop, unpivot and append data from external file

Question

I am trying to accomplish below with PowerQuery M

1) Open a set of files (located in a folder on a shared drive)

2) Get the data in a sheet called "BC Ongoing Projects"

3) Rearrange the data (mainly unpivot a set of columns (column name changes for each file) 4) Append them under each other

I have tried with below code, but it gives me an error "The name 'ReOrganizedTable' wasn't recognized. Make sure it is spelled correct."

let

//Get data from file in folder
Source = Folder.Files(Link_StaffDataLocation),
#"Get the files" = Table.SelectRows(Source, each [Extension] = ".csv" or [Extension] = ".txt" or Text.StartsWith([Extension], ".xls")),
#"Removed Columns" = Table.RemoveColumns(#"Get the files",{"Date accessed", "Date modified", "Date created", "Attributes", "Folder Path"}),
GetExcelContentIntoColumn = Table.AddColumn(#"Removed Columns", "Custom", each if Text.StartsWith([Extension], ".xls") then Excel.Workbook( [Content]) else null),
ExpandContent = Table.ExpandTableColumn(GetExcelContentIntoColumn, "Custom", {"Name", "Data", "Kind"}, {"Custom.Name", "Custom.Data", "Custom.Kind"}),
#"Filtered Rows" = Table.SelectRows(ExpandContent, each ([Custom.Name] = "BC Ongoing Projects") ),

//Create files name list
FileNameListStep1 = Table.Column(#"Filtered Rows","Name"),
FileNameListStep2 = List.Skip(FileNameListStep1,1),

//Filter on first filename
IterationFunction = (IterationList) =>
let
    CurrentFilenameList = List.Skip(IterationList,1),
    CurrentFilename = CurrentFilenameList{0},
    FilterOneDataSetAtATime = Table.SelectRows(#"Filtered Rows", each ([Name] = CurrentFilename)),
    // Make necessary adjustments to data
    #"Custom Data1" = #"Filtered Rows"{0}[Custom.Data],
    #"Filtered Rows1" = Table.SelectRows(#"Custom Data1", each ([Column1] <> null and [Column1] <> "BC Ongoing Projects" and [Column1] <> "Filters Used To Select These Practitioners:" and [Column1] <> "Pract" and [Column1] <> "Scheduled Hours" and [Column1] <> "Total:")),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",{"Column5"}),
    #"Replaced Value_DK" = Table.ReplaceValue(#"Removed Columns1","DK","",Replacer.ReplaceText,{"Column4"}),
    #"Replaced Value_SE" = Table.ReplaceValue(#"Replaced Value_DK","SE","",Replacer.ReplaceText,{"Column4"}),
    #"Replaced Value" = Table.ReplaceValue(#"Replaced Value_SE",null,0,Replacer.ReplaceValue,{"Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14", "Column15", "Column16", "Column17"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type"),
    //Create List of week dates    
    MondayInWeekStep1 = Table.ColumnNames(#"Promoted Headers"),
    MondayInWeekStep2 = List.Skip(MondayInWeekStep1,4),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Name", "Client", "Type", "Key"}, "MondayInWeek", "Staff Hours"),
    ReOrganizedTable = Table.TransformColumnTypes(#"Unpivoted Columns",{{"MondayInWeek", type date}, {"Staff Hours", Int64.Type}})
in
    ReOrganizedTable,
    AppendTablesTogether = Table.Combine(ReOrganizedTable,IterationFunction(CurrentFilenameList))
in
AppendTablesTogether

Very much appreciate amny help you can give me

BR Jonas

Answer 1

ReOrganziedTable only exists in the IterationFunction you defined. You probably want to do the following:

1. Move the CurrentFilenameList step to the line before IterationFunction.
2. Change IterationFunction to take in CurrentFilename instead of IterationList. In other words, remove the CurrentFilename step in IterationFunction and change (IterationList) => to (CurrentFilename) =>
3. Use List.Transform to build a list of tables, then convert that list to a table. The List.Transform part should do something like List.Transform(FileNameListStep2, (filename) => IterationFunction(filename))