append connection only queries

ThomasDay actually, making one query per data set is an acceptable solution, you can consider these tables "backing queries" and choose not to load them (right click -> uncheck Loaded to report). You can even organize them into a folder for browsing needs.

Then, you can have one master query that uses Table.Combine() for all these backing queries, and load that one to the report.

This is probably the easist solution as you don't have to write a single line of M code.

Now, if you want to do all of this in one query, you are going to have to write something. Starting from your query:

let
Source1 = Csv.Document(File.Contents("C:\Users\thomas\Dropbox\FTRatings\FTRatingsData-Models\DataLoadToPrimaryModelFile\HOSP10_2014_RPT.CSV"),[Delimiter=",", Encoding=1252]),
#"Renamed Columns" = Table.RenameColumns(Source1, {{"Column1", "ReptRecNo"}, {"Column2", "CtrlType(2)"}, {"Column3", "ProvdrNo"}, {"Column4", "NPI"}, {"Column5", "ReptStatus(1)"}, {"Column6", "FYBeginDt"}, {"Column7", "FYEndDt"}}),
#"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Column8", "HCRISDt"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"ReptRecNo", "CtrlType(2)", "ProvdrNo", "FYBeginDt", "FYEndDt", "HCRISDt"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"ProvdrNo", Int64.Type}, {"FYBeginDt", type date}, {"FYEndDt", type date}, {"HCRISDt", type date}, {"ReptRecNo", Int64.Type}})
in
#"Changed Type1"

Let's parameterize the CSV path:

let
 GetData = (path) => let
 Source1 = Csv.Document(File.Contents(path),[Delimiter=",", Encoding=1252]),
 #"Renamed Columns" = Table.RenameColumns(Source1, {{"Column1", "ReptRecNo"}, {"Column2", "CtrlType(2)"}, {"Column3", "ProvdrNo"}, {"Column4", "NPI"}, {"Column5", "ReptStatus(1)"}, {"Column6", "FYBeginDt"}, {"Column7", "FYEndDt"}}),
 #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Column8", "HCRISDt"}}),
 #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"ReptRecNo", "CtrlType(2)", "ProvdrNo", "FYBeginDt", "FYEndDt", "HCRISDt"}),
 #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"ProvdrNo", Int64.Type}, {"FYBeginDt", type date}, {"FYEndDt", type date}, {"HCRISDt", type date}, {"ReptRecNo", Int64.Type}})
 in
 #"Changed Type1",
 FY14 = GetData("C:\Users\thomas\Dropbox\FTRatings\FTRatingsData-Models\DataLoadToPrimaryModelFile\HOSP10_2014_RPT.CSV")
in
 FY14

Then add FY15, FY16, etc:

let
  GetData = (path) => let
    Source1 = Csv.Document(File.Contents(path),[Delimiter=",", Encoding=1252]),
    #"Renamed Columns" = Table.RenameColumns(Source1, {{"Column1", "ReptRecNo"}, {"Column2", "CtrlType(2)"}, {"Column3", "ProvdrNo"}, {"Column4", "NPI"}, {"Column5", "ReptStatus(1)"}, {"Column6", "FYBeginDt"}, {"Column7", "FYEndDt"}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Column8", "HCRISDt"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"ReptRecNo", "CtrlType(2)", "ProvdrNo", "FYBeginDt", "FYEndDt", "HCRISDt"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"ProvdrNo", Int64.Type}, {"FYBeginDt", type date}, {"FYEndDt", type date}, {"HCRISDt", type date}, {"ReptRecNo", Int64.Type}})
  in
    #"Changed Type1",
  FY14 = GetData("C:\Users\thomas\Dropbox\FTRatings\FTRatingsData-Models\DataLoadToPrimaryModelFile\HOSP10_2014_RPT.CSV"),
  FY15 = GetData("C:\Users\thomas\Dropbox\FTRatings\FTRatingsData-Models\DataLoadToPrimaryModelFile\HOSP10_2015_RPT.CSV"),
  FY16 = GetData("C:\Users\thomas\Dropbox\FTRatings\FTRatingsData-Models\DataLoadToPrimaryModelFile\HOSP10_2016_RPT.CSV")
  ...

in
  FY16 

Then combine them into the final result

let
  GetData = (path) => let
    Source1 = Csv.Document(File.Contents(path),[Delimiter=",", Encoding=1252]),
    #"Renamed Columns" = Table.RenameColumns(Source1, {{"Column1", "ReptRecNo"}, {"Column2", "CtrlType(2)"}, {"Column3", "ProvdrNo"}, {"Column4", "NPI"}, {"Column5", "ReptStatus(1)"}, {"Column6", "FYBeginDt"}, {"Column7", "FYEndDt"}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Renamed Columns",{{"Column8", "HCRISDt"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"ReptRecNo", "CtrlType(2)", "ProvdrNo", "FYBeginDt", "FYEndDt", "HCRISDt"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"ProvdrNo", Int64.Type}, {"FYBeginDt", type date}, {"FYEndDt", type date}, {"HCRISDt", type date}, {"ReptRecNo", Int64.Type}})
  in
    #"Changed Type1",
  FY14 = GetData("C:\Users\thomas\Dropbox\FTRatings\FTRatingsData-Models\DataLoadToPrimaryModelFile\HOSP10_2014_RPT.CSV"),
  FY15 = GetData("C:\Users\thomas\Dropbox\FTRatings\FTRatingsData-Models\DataLoadToPrimaryModelFile\HOSP10_2015_RPT.CSV"),
  FY16 = GetData("C:\Users\thomas\Dropbox\FTRatings\FTRatingsData-Models\DataLoadToPrimaryModelFile\HOSP10_2016_RPT.CSV")
  Combine = Table.Combine({ FY14, FY15, FY16, ... })
in
  Combine

You can even dynamically generate the FY** list and have it respond to a file system change, and automatically pick up new CSV files in a folder.

(ps., i haven't tested the queries above, so there maybe syntax errors, but you get the idea)

Regards,

PQ