Forum Discussion

ThomasDay's avatar
ThomasDay
Impactful Individual
10 years ago
Solved

append connection only queries

Each quarter, CMS publishes new financial data for hospitals.  The reports correctly stack up to each other--so it's an ideal append situation.  They are reasonably large (20mm rows per year and thre...
  • pqian's avatar
    pqian
    10 years ago

    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

ThomasDay's avatar
ThomasDay
Impactful Individual
10 years ago

I'll be interested to hear as well, though all of my queries are stable and have handled new files I've put in the folders.  Seems like it must be related to the data source though since the code works well with putting new, identically configured files into a folder.  Is that possible?

Kane's avatar
Kane
Helper I
10 years ago

Hi, ThomasDay

 

With pqian's method (the nested "let-in" structure), I can easily get data from different files located in and append them to one query, based on which I can create my measures, visuals and reports.

I can also refresh the data in PowerBI Desktop, everything works well. However, after published my report to the PowerBI online, I cannot schedule a refresh for the dataset online.

 

The  problem can be solved by removing the nested "let-in" structure.

I am puzzled on this. I like pqian's method, but it seems that it will lead to a refesh problem. Hope someone can help me on this.

 

Best Regards,

Kane

 

 

  • ImkeF's avatar
    ImkeF
    Community Champion
    10 years ago

    This doesn't seem to be an issue with the method, but with the datasource.

     

    The code you've provided is trowing 404-error-messages in PBI Desktop here already.

    • Kane's avatar
      Kane
      Helper I
      10 years ago

      Hello, ImkeF,

       

      I do not think it is the issue of datasource.

       

      Let's try another datasource: https://en.wikipedia.org/wiki/ISO_3166-1

       

      I tried the following two code style. Both of them work well in PowerBI Desktop (I can refresh the data by cliking the Refresh Button mannually).

      Unfortunately, once I published the two codes (reports) to the PowerBI online, it shows that I cannot schedule a refresh for the first one.

      here is the information:

       

       

      Code Style 1:

       

      let
    GetData = (Path) =>
    let
        Source = Web.Page(Web.Contents(Path)),
        Data0 = Source{0}[Data],
        #"Changed Type" = Table.TransformColumnTypes(Data0,{{"English short name (upper/lower case)", type text}, {"Alpha-2 code", type text}, {"Alpha-3 code", type text}, {"Numeric code", Int64.Type}, {"Link to ISO 3166-2 subdivision codes", type text}})
    in
    #"Changed Type",
    Web0 = GetData("https://en.wikipedia.org/wiki/ISO_3166-1"),
    Web1 = GetData("https://en.wikipedia.org/wiki/ISO_3166-1"),
    WebCombine = Table.Combine({Web0, Web1})
in
    WebCombine

       

       

      Code Style 2:

      let
    Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/ISO_3166-1")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"English short name (upper/lower case)", type text}, {"Alpha-2 code", type text}, {"Alpha-3 code", type text}, {"Numeric code", Int64.Type}, {"Link to ISO 3166-2 subdivision codes", type text}})
in
    #"Changed Type"

       

      THANK you !

       

      Best Regards,

      Kane