Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 three files).
I just tried to append by duplicating the initial year's connection only query--giving it a new name--connecting to the new source, and clicking "append query". To my surprise, it made a separate table with dotted line connections to the original table as well as to the tables it is connected to.
OK, that's harder to read than the situation. It didn't append the data to the same table--it made another table and wired it to the others. Won't be long before I won't know which way is up!
Is this the Power Bi way?
If so, I'll probably try to merge the .csv files first and then use the original query.
Thanks,
Tom
Solved! Go to Solution.
@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
Yes, this is Power BI - as every query will be loaded to the model.
So in order to proceed as you've already suggested, you copy the step(s) that load the data instead of duplicating the whole query.
As long as you stay in one query, the output will alway stay one table that is connected to the model.
Your combine would look like this: Table.Combine({"LastStepOfFirstPull", "LastStepOfSecondPull", "LastStepOf3rdPull",...})
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
@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
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?
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
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.
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
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
Hi @Kane,
seems to be a current limitation actually: https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/10927416-web-contents-should-supp...
Wasn't aware of that..
Imke Feldmann (The BIccountant)
If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!
How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries
@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.
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
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |