Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
I couldn't find a solution for this so far. Any help would be appreciated!
Solved! Go to 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),
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
14 | |
13 | |
13 | |
12 | |
12 |