Forum Discussion

prat91's avatar
prat91
Frequent Visitor
8 years ago

I have tried that, but unfortunately it doesnt work, even once I have expanded or drilled down on the columns. From my understanding there is a need to create a parameter and function, but not sure on the process of doing so. 

stretcharm's avatar
stretcharm
Icon for Memorable Member rankMemorable Member
8 years ago

Try this

 

let
    Source = Excel.Workbook(Web.Contents("go.microsoft.com/fwlink/?LinkID=521962"), null, true),
    financials_Table = Source{[Item="financials",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(financials_Table,{{"Segment", type text}, {"Country", type text}, {"Product", type text}, {"Discount Band", type text}, {"Units Sold", type number}, {"Manufacturing Price", Int64.Type}, {"Sale Price", Int64.Type}, {"Gross Sales", type number}, {"Discounts", type number}, {" Sales", type number}, {"COGS", type number}, {"Profit", type number}, {"Date", type date}, {"Month Number", Int64.Type}, {"Month Name", type text}, {"Year", Int64.Type}})
in
    #"Changed Type"

I connected to the site using the web connector, then clicked right mouse menu over the document and Selected Excel.

 

If you have lots of documents you can use functions to loop over the documents. There are several posts that explain how.

e.g.

https://powerbi-pro.com/iterating-over-an-unknown-number-of-pages-in-power-query/

 

Phil