Forum Discussion

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

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,

ThomasDay

Impactful Individual

10 years ago

Thank you, this sounds promising!.. (As a disclaimer, I am still plowing thru the definitive DAX guide--M is for Data Monkey is next but it doesn't seem like a "M" language book. )

If I understand what you're saying...with the Query shown below as it exists now--I copy everything below the "let", change the name to Source2 = and then add a Table.Combine statement? (This is a connection only, if that matters)

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"

Then the Table.Combine? What's the table name for the combine statement?

Thank you, Tom

Thanks,

Tom

pqian

Microsoft Employee

10 years ago

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,

ThomasDay
Impactful Individual
10 years ago
pqian--this looks great. I'm not at my db tomorrow until mid day, but will then experiment and report back. (What does the "in" line at the end of the query do if I can ask?)

So there is only one query, the Table.Combine would run and append each of the tables resulting from the GetData parameters into one data model table. Brilliant...

Thank you...I'm looking forward to giving this a go,
Tom

PS: is there an "M" reference or resource you'd recommend for when I've finished the Definitive DAX Guide.
- pqian
  Microsoft Employee
  10 years ago
  ThomasDay
  The "in" is the return of the closure. You can actually change this to be
  let FY14 = .., FY15 = .., .. in Table.Combine({FY14, FY15, ...})
  which is effectively:
  var FY14, FY15...
  return Table.Combine({FY14, F15, ...})
  
  You can find a comprehensive guide to the M language on MSDN
  https://msdn.microsoft.com/en-us/library/mt211003.aspx
  - ThomasDay
    Impactful Individual
    10 years ago
    pqian This is perfect. Thank you very much...worked like a champ. The records appended, connections remained, field names changed correctly, columns data types changed properly (seems the order is different not that I should care). There's only some cleanup to make the queries do all the things I want to every column---and I'll just hit the refresh and off I go.
    
    Thank you again,
    Tom
Kane
Helper I
10 years ago
Hi, pqian You provided a very good solution to import multiple data set in one query. I like this style very much. Unfortunately, I met a refresh problem when I published my report which used your method to the Power BI online. When I tried to setup a refresh schedule, it showed that the data set cannot be refreshed automatically since one or more data source are not supported. Did you ever meet this problem ? And do you have any idea to solve this problem ? ------------------------ Here is a simple example. My problem can be reproduced with a report which has only the following query. let GetData = (Path) => let Source = Web.Page(Web.Contents(Path)), Data0 = Source{0}[Data], #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Header", type text}, {"Overall rank", Int64.Type}, {"State", type text}, {"Cost of living", Int64.Type}, {"Crime rate", Int64.Type}, {"Community well-being", Int64.Type}, {"Health care quality", type text}, {"Tax rate", Int64.Type}, {"Weather", Int64.Type}}) in #"Changed Type", Web0 = GetData("http://www.bankrate.com/finance/retirement/best-places-retire-how-state-ranks.aspx"), Web1 = GetData("http://www.bankrate.com/finance/retirement/best-places-retire-how-state-ranks.aspx"), Weekly = Table.Combine({Web0, Web1}) in Weekly Thank you ! Kane
Kane
Helper I
10 years ago
Hi, pqian You provided a very good solution to import multiple data set in one query. I like this style very much. Unfortunately, I met a refresh problem when I published my report which used your method to the Power BI online. When I tried to setup a refresh schedule, it showed that the data set cannot be refreshed automatically since one or more data source are not supported. Did you ever meet this problem ? And do you have any idea to solve this problem ? ------------------------ Here is a simple example. My problem can be reproduced with a report which has only the following query. let GetData = (Path) => let Source = Web.Page(Web.Contents(Path)), Data0 = Source{0��}[Data], #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Header", type text��}, {"Overall rank", Int64.Type��}, {"State", type text��}, {"Cost of living", Int64.Type��}, {"Crime rate", Int64.Type��}, {"Community well-being", Int64.Type��}, {"Health care quality", type text��}, {"Tax rate", Int64.Type��}, {"Weather", Int64.Type��}��}) in #"Changed Type", Web0 = GetData("http://www.bankrate.com/finance/retirement/best-places-retire-how-state-ranks.aspx"), Web1 = GetData("http://www.bankrate.com/finance/retirement/best-places-retire-how-state-ranks.aspx"), Weekly = Table.Combine({Web0, Web1��}) in Weekly Thank you ! Kane
- 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
    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

Forum Discussion

append connection only queries

Related content

Dataflow - Append

Append Queries 3 different data sources

Append Queries Broken?

Append Queries and Remove Duplicate

Append query vs Append query as New