cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Table is blank after query

I am trying to import some data from excel and everything seems to work in the Power Query windows but when I close and apply the query, the resulting table in Power Bi Desktop is blank (See screenshot at the end of this message)

 

I downloaded gold price data from https://www.gold.org/goldhub/data/gold-prices 

The data includes gold prices from 1978 to date. I imported it using the following steps:

 

 

let
    Source = Excel.Workbook(File.Contents("D:\DATA\Gold Prices.xlsx"), null, true),
    Daily_Sheet = Source{[Item="Daily",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Daily_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Last updated 22/03/2020", Int64.Type}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type any}, {"Column12", type any}, {"Column13", type any}, {"Column14", type any}, {"Column15", type any}, {"Column16", type any}, {"Column17", type any}, {"Column18", type any}, {"Column19", type any}, {"Column20", type any}, {"Column21", type any}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type any}, {"Column27", type any}, {"Column28", type any}, {"Column29", type any}, {"Column30", type any}, {"Column31", type any}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Last updated 22/03/2020", "Column2", "Column3"}),
    #"Removed Top Rows" = Table.Skip(#"Removed Columns",5),
    #"Promoted Headers1" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers1",{{"Column1", type any}, {"USD", type any}, {"EUR", type any}, {"JPY", type any}, {"GBP", type any}, {"CAD", type any}, {"CHF", type any}, {"INR", type any}, {"CNY", type any}, {"USD_1", type any}, {"TRY", type any}, {"SAR", type any}, {"IDR", type any}, {"AED", type any}, {"THB", type any}, {"VND", type any}, {"EGP", type any}, {"KRW", type any}, {"EUR_2", type any}, {"RUB", type any}, {"USD_3", type any}, {"ZAR", type any}, {"CNY_4", type any}, {"CAD_5", type any}, {"AUD", type any}, {"G5 CCY", type any}, {"PROD IDX", type any}, {"CONS IDX", type any}}),
    #"Removed Top Rows1" = Table.Skip(#"Changed Type1",2),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Top Rows1",{{"Column1", "Date"}}),
    #"Removed Columns2" = Table.RemoveColumns(#"Renamed Columns",{"EUR", "JPY", "GBP", "CAD", "CHF", "INR", "CNY", "USD_1", "TRY", "SAR", "IDR", "AED", "THB", "VND", "EGP", "KRW", "EUR_2", "RUB", "USD_3", "ZAR", "CNY_4", "CAD_5", "AUD", "G5 CCY", "PROD IDX", "CONS IDX"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Removed Columns2",{{"Date", type date}})
in
    #"Changed Type2"

 

 

PBI desktop is blank after queryPBI desktop is blank after query

 

 

What could be the problem?

1 REPLY 1
v-yuta-msft
Community Support
Community Support

@Anonymous ,

 

I could not find the sample .xlsx file via the link, could you please share that sample file with onedrive for business?

 

Regards,

Jimmy Tao

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors