Column of table not found

[Nks_Mr]

Hello everbody,

I want to transform the data below into 3 seperate lines (now it is one cell with three lines)

 with this code

let
    Source = Excel.Workbook(File.Contents(myPath"), null, true),
    mix = List.Zip({Text.Split(Source[Name]{0},"#(lf)"),Text.Split(Source[House number]{0},"#(lf)"),Text.Split(Source[Country]{0},"#(lf)")}),
    #"Converted to Table" = Table.FromList(mix, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), "|"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Name", "House number", "Country"})
in
    #"Split Column by Delimiter"

However I get this Error:  
Expression.Error: The column 'House number' of the table wasn't found.
Details: House number

I also did it without House number and only Name and Country, then it said  column "Country" not found.

Do you have any recommendations?

Thank you very much in advance!

[Anonymous · Accepted solution]

Hi @Nks_Mr ,

I use the same code, but I have no problem.

Please make sure your column name is consistent with the name in the code.

Please check out your applied steps in the Power Query. I renamed the column in my table.

So, when I still use ‘Source’ as data source, it has the same issue as yours.

Make sure you are using the latest data source.

Best regards,

Lucy Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PijushRoy]

Hi @Nks_Mr 
M Code is correct but maybe you have some extra space in the column header
Please double-click on the Column header and copy the name, then paste it in the below-highlighted place

[Nks_Mr]

Done, unfortunately has not worked 😕

[rajendraongole1]

Hi @Nks_Mr -  the column names in the source table might not exactly match what you're referencing in the code. Power Query is case-sensitive, and any extra spaces or typos in the column names could result in this error.

can you try the below attached modified pq code. 

Hope it works. 

[Nks_Mr]

Thank you!

However this doesn't seem to work. 

[PijushRoy]

Hi @Nks_Mr 
It depends on data, please share the data in Google Drive, One Drive or paste as excel format

[Nks_Mr]

Hi @PijushRoy 

https://docs.google.com/spreadsheets/d/1bB-HN2uYMncPviu1kjzZ29YyyrlPTuWl/edit?usp=drive_link&ouid=11...

Can you work with this?

[PijushRoy]