Continuously compiling excel data source?

[DSwezey]

Hi there,

We have salespeople generate a quote anytime there's a new prospected client. This quote is then saved in a folder as an Excel file.

The formatting of the quote excel files are all identical. The only difference with each file is obviously the name of the file itself. 

I need to grab the data from each quote and any new quote added to the folder going forward. Is there a way to have Power BI recognize new excel files in a folder and then add that data into a continuos compilation?

EX:

The folder contains the file:

"ABC Company Quote"

Now I add a new file so there is now:

"ABC Company Quote"

"123 Company Quote"

I want Power BI to not only recognize the additional "123 Company Quote" file but to compile its data with the existing "ABC Company Quote"

[AlexisOlson · Accepted solution]

Loading from a folder should be able to handle this well.

https://exceloffthegrid.com/power-query-import-all-files-in-a-folder/

https://docs.microsoft.com/en-us/power-query/connectors/folder

https://www.youtube.com/watch?v=nPlrQUbEn4o

https://www.youtube.com/watch?v=UY3hIV-THcg

[DSwezey]

Ahh yes! Okay, so I was able to use the "combine files in folder" source setting and every time I refresh my dataset it updates. Amazing thank you! 

Now I am left with another issue I haven't dealt with yet.

When the data is brought in the column names and data continue down the rows. For Example:

I have 4 columns in the Quoted excel sheet: First Name, Last Name, Quoted Amount, Date.

Lets say there are 2 files brought in. This is how it would look after it's brought into Power BI:

If I transpose the data it will then look like this which is not very useful:

How do I transpose the data so the columns don't duplicate like below:

Thank you!

[AlexisOlson]

Try pivoting instead of transposing.

https://docs.microsoft.com/en-us/power-query/pivot-columns

[DSwezey]

The issue I am having with that is it's just counting the number of cells in each category rather than pasting the actual value.

Also, if I select "Don't Aggregate" I get these errors

[AlexisOlson]

Ah, right.

The issue here is that without some sort of indexing, it doesn't know that the first four rows belong together in one row and the last four rows belong in another.

You can fix that by adding an appropriate index:

Here's an example of you you can define this assuming you've got a consistent number of rows per chunk:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcsssKi5R8EvMTVXSUXJJzFOK1YlW8klEiAXnZuaUZICFA0vzS1JTFBxz80vzSoBShgYGBmAJl8QSkFJTfUMjfSMDIyOwIFAATKPY4JxRlFmMYUdAfk5OfnI2dkuMTNEtMYRaEgsA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Col1 = _t, Col2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Col2] <> "")),
    #"Added Index" = Table.AddIndexColumn(#"Filtered Rows", "Index", 0, 1, Int64.Type),
    #"Integer-Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each Number.IntegerDivide(_, 4), Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Integer-Divided Column", List.Distinct(#"Integer-Divided Column"[Col1]), "Col1", "Col2")
in
    #"Pivoted Column"

Result: