cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
Gaby_Scarcella
New Member

How to use a list of values from Excel cell in Csv.Document function?

Hi all!

 

In order to parse fixed-width files with variable number of characters that marks the start of each column, I'd like to use a list of values stored in a cell in an Excel document. To do so, I retrieved the values from the cell and converted it in a list of values, like this:

ColumnsFromExcel = Excel.CurrentWorkbook(){[Name="ColumnasSeparadorasRCV"]}[Content]{0}[Column1],

#"ColumnsList (text)" = Text.Split(ColumnsFromExcel, ", "),

#"ColumnsList (num)" = List.Transform(#"ColumnsList (text)", each Number.FromText(_)),

 

When I try to use the resulting list from Excel cell, Csv.Document function returns just one column, however, if using a typed list of values, the function returns the expected number of columns. Both lists seems to be identical:

Crop_CaracteresDesdeInicio.jpg

Crop_ColumnsList (num).jpg

 

I couldn't find a solution for this so far. Any help would be appreciated!

1 ACCEPTED SOLUTION

Thanks. This makes it clear exactly what's going on.

 

In your first line, it's interpreting "ColumnsList (num)" as a literal string rather than a reference to a list. In order to disambiguate, try it with an octothorpe (#) in front.

Source = Csv.Document(File.Contents(FilePath & FileName),null,#"ColumnsList (num)",null,1252),

View solution in original post

4 REPLIES 4
AlexisOlson
Super User
Super User

When I try to use the resulting list from Excel cell, Csv.Document function returns just one column

I'm not following how exactly you're using the lists. Can you share the code for the step with the Csv function?

Hi @AlexisOlson.
Thanks for the fast reply.

I'm trying to use this code:
Source = Csv.Document(File.Contents(FilePath & FileName),null,"ColumnsList (num)", null,1252),


instead of:


Source = Csv.Document(File.Contents(FilePath & FileName),null,{0, 9, 28, 36, 40, 45, 55, 65, 71},null,1252),

Thanks. This makes it clear exactly what's going on.

 

In your first line, it's interpreting "ColumnsList (num)" as a literal string rather than a reference to a list. In order to disambiguate, try it with an octothorpe (#) in front.

Source = Csv.Document(File.Contents(FilePath & FileName),null,#"ColumnsList (num)",null,1252),

That did the trick, thank you @AlexisOlson for pointing the error in my code.

Really appreciate your help.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors