Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nvalPBI
New Member

Trying to Extract a column from Excel for Power BI Dashboard

I have connected to an excel sheet in onedrive folder from Power BI. Extracting the first column from the excel sheet worked. When I added the second line to extract the second column , thats when I got the error. 
    Source = Excel.Workbook(Web.Contents("")
    #"345_Sheet" = Source{[Item=Source{0}[Item],Kind="Sheet"]}[Data],
    #"346_Sheet" = Source{[Item...


 

I am getting an error   Expression.Error: There weren't enough elements in the enumeration to comple...

 

Can anyone help?

5 REPLIES 5
nvalPBI
New Member

Data is very simple. I have an excel sheet with 2 columns. I created some sample data to see if this can be extracted to display on the  PBI dashboard.

nvalPBI_0-1675455060370.png

 

AlB
Super User
Super User

Hi @nvalPBI 

Can you show some sample data?

The links provided do not work

 

SU18_powerbi_badge

Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

 

Looks like data wasnt refreshed. After refreshing the data I get this error now trying to read the second column

nvalPBI_0-1675458692613.png

 

Hi @nvalPBI - the following code does not look right:

= Source{[Item=Source{1}[Item],Kind="Sheet"]}[Data]

 The following is expected:

= Source{[Item="SheetName",Kind="Sheet"]}[Data]

I think the inclusion of the following code results in the selection of Table object (i.e. Kind="Table").  Note that {1} will return the second row of table because the index starts counting from 0.

= Source{1}[Item]  //Returns name of a table not the name of Sheet - is "Table1" the name of this table?

Or this:
= Source{[Item=Source{1}[Item],Kind="Table"]}[Data] 

 

P.S. you might try change to use SharePoint.Files - Wink!

Since the fist column was retreived using the code

 Source{[Item=Source{0}[Item],Kind="Sheet"]}[Data]

I thought the second column should be Source {1}.  How do you refer to a second colum in a table??  Yes, it is Table1

There is no change in the worksheet and table from first column to second column. Everything same except the columns. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors