Help With Model Shaping / Query From Folder

a68tbird
Posts: 32
Joined: Sat Mar 19, 2016 4:58 am
Location: Toronto, ON

Help With Model Shaping / Query From Folder

Postby a68tbird » Thu Sep 29, 2016 2:00 am

Hello All -
I'm having some trouble trying to build my data model, and want to throw this out to the community for some input.

I receive a weekly report in CSV format. I'm not able to attach samples of the CSVs here, so please download 2 samples here:

http://occstoragemedia.blob.core.window ... 091916.CSV
http://occstoragemedia.blob.core.window ... 092616.CSV

These files are album sales reports, and each sheet contains three different reports: Billboard Top 200, Top New Artist Albums, and Digital Songs. I am only interested in retaining the data from the Billboard Top 200 list. Essentially, only the top 203 rows are what I'm interested in. I have no problem cleaning up one file, however my problem arises when a new file is added to the folder. I want my data model to grow each week by the additional 200 rows. Here is my Query (obviously you'll have to change the file source to be applicable to you):

Code: Select all

let
    Source = Folder.Files("PUT YOUR SOURCE HERE"),
    #"Combined Binaries" = Binary.Combine(Source[Content]),
    #"Imported CSV" = Csv.Document(#"Combined Binaries",[Delimiter = ",",Columns=14, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(#"Imported CSV",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows"),
    #"Removed Columns" = Table.RemoveColumns(#"Promoted Headers",{" "}),
    #"Kept First Rows" = Table.FirstN(#"Removed Columns",200),
    #"Changed Type1" = Table.TransformColumnTypes(#"Kept First Rows",{{"Weeks On", type number}, {"2W Rank", type number}, {"LW Rank", type number}, {"TW Rank", type number}, {"TW Total Activity", type number}, {"% CHG", type number}, {"LW Total Activity", type number}, {"TW Album Sales", type number}, {"TW Song Sales", type number}, {"TW Stream Activity", type number}})
in
    #"Changed Type1"
 


The trouble is that I really don't want to combine the binaries - I want to append the new weekly file to what has already been built.

Another snag is that the rows have no related date. There is a date that appears in row 2 (Week Ending: MM/DD/YYYY) that could be used as an added column, but I'm not quite sure how I could use or add that information.

Thank you very much for any help.

MattAllington
Posts: 928
Joined: Sun May 04, 2014 4:01 pm
Location: Sydney, Australia

Re: Help With Model Shaping / Query From Folder

Postby MattAllington » Thu Sep 29, 2016 9:23 am

Could the method I describe here work for you? http://exceleratorbi.com.au/combine-exc ... -method-1/

It allows you to add new files to a folder and then they are all appended together.

As for the missing date, take a look at this article I wrote a couple of months ago. http://exceleratorbi.com.au/convert-a-c ... wer-query/

Post back if you need more help
Matt Allington is Self Service BI Consultant, Trainer and Author of the book "Supercharge Power BI".
https://exceleratorbi.com.au/power-bi-online-training/

a68tbird
Posts: 32
Joined: Sat Mar 19, 2016 4:58 am
Location: Toronto, ON

Re: Help With Model Shaping / Query From Folder

Postby a68tbird » Fri Sep 30, 2016 3:24 am

Wow! Matt - this is fantastic stuff! Does exactly what I needed it to do. I was able to process 90 weeks worth of data, and as new files are submitted each week, I just have to drop them into the referenced folder, and that data gets processed as well.

Now...just to figure out how best to plot/present this information so it's useful! :D

Thanks very much
Travis

a68tbird
Posts: 32
Joined: Sat Mar 19, 2016 4:58 am
Location: Toronto, ON

Re: Help With Model Shaping / Query From Folder

Postby a68tbird » Tue Oct 04, 2016 4:50 am

Hi Matt,
I've adapted these steps that you described for another project - combining an entire year's worth of invoices from one of our partners into a manageable data model. The multiple weekly invoice files that I receive are, from what I believe, saved/created as a PDF file first and then converted to Excel. The Excel files are impossible to work with on their own: 40+ columns, most of them with null values, headers and footers throughout the document, merged columns, and dozens of pages long. I was able to import 172 files, clean them all up and now have a nice usable data model. However...there were 6 files where the formatting was a little bit off, and it would break my query. The structure is not identical to the other files. I can clean each of these manually, but I'm not sure how I could now add these back into the main data model. I tried using Append Query, but receive this error:

Code: Select all

Formula.Firewall: Query 'foo' (step 'Appended Query') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.


Also, because I'm using the "From Folder" query method, these troublesome files are saved in a separate folder.

Thoughts?
Thanks!
Travis

a68tbird
Posts: 32
Joined: Sat Mar 19, 2016 4:58 am
Location: Toronto, ON

Re: Help With Model Shaping / Query From Folder

Postby a68tbird » Tue Oct 04, 2016 5:31 am

Figured it out. I didn't see that "Combine Queries" action under the New Query tab. That's exactly what I needed, and have successfully appended the two queries.

Thanks!


Return to “Power Query”

Who is online

Users browsing this forum: No registered users and 5 guests

cron