Forum Discussion
Convert table that is using #lf (line feed)
Some similarity with DataNinja777 's but split/zip/convert to table in one line.
See attached Excel workbook.
Code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // Keep note of original column headers and their order: ColmHdrs = Table.ColumnNames(Source), AddedCustom = Table.AddColumn(Source, "Custom", each Table.FromRows(List.Zip({Text.Split([Unit], "#(lf)"),Text.Split([qty], "#(lf)"),Text.Split([Year], "#(lf)")}),{"Unit","qty","Year"})), RemovedColumns = Table.RemoveColumns(AddedCustom,{"Unit", "qty", "Year"}), ExpandedCustom = Table.ExpandTableColumn(RemovedColumns, "Custom", {"Unit", "qty", "Year"}, {"Unit", "qty", "Year"}), // use the original column order from th ColmHdrs step: ReorderedColumns = Table.ReorderColumns(ExpandedCustom,ColmHdrs) in ReorderedColumnsYou can add a step like:
ReplacedValue = Table.ReplaceValue(ExpandedCustom,each [qty],each try Number.From([qty]) otherwise [qty] ,Replacer.ReplaceValue,{"qty"})
This leaves the column as type Any but the individual values will be type number or type text depending on content, and so they'll appear in Excel as numbers/text in cells (justified right if numbers and justified left if text).
For Year you could do something similar. They could be combined in a single step but that would require (I think) an inline function but I've been lazy.
Hi DanFromMontreal ,
It sounds like you've run into a common data formatting issue, which is a perfect scenario for using Power Query. Manually adjusting a table with 22 columns and 857 rows would be incredibly tedious, but Power Query is specifically designed to handle these kinds of data transformations efficiently. You can convert your table in just a few steps.
First, you'll need to load your data into the Power Query Editor. You can do this by clicking anywhere inside your data table in Excel, navigating to the Data tab on the ribbon, and then selecting From Table/Range. This action will open the Power Query Editor with your data ready for transformation.
The main strategy is to temporarily combine the columns that contain multiple lines of data. This ensures that the corresponding values from each line stay together. To do this, go to the Add Column tab and select Custom Column. You will create a new column, which you can name Combined, using a formula to "zip" the related columns together.
List.Zip({
Text.Split([Unit], "#(lf)"),
Text.Split([qty], "#(lf)"),
Text.Split([Year], "#(lf)")
})This M code splits each of the multi-line columns by the line feed character #(lf) into separate lists and then zips those lists together. If you have more columns to process, you simply add another Text.Split([ColumnName], "#(lf)") line to the formula. After applying this, you'll see a new column where each cell contains the word List.
Next, you will expand this new Combined column to create the rows you need. Click the expand icon in the column header and choose Expand to New Rows. This will transform your table, creating a distinct row for each item that was previously on a new line. The Combined column will still show List in each cell. Click the expand icon again, but this time select Extract Values... to split the combined data back into separate columns.
Finally, you just need to clean up the table. Rename the newly generated columns (e.g., Combined.1, Combined.2) back to their original names like Unit and qty. Then, you can remove the original multi-line columns that you no longer need. It's also a good practice to check and set the correct data types for your columns, such as setting qty and Year to Whole Number. Once you're done, go to the Home tab and click Close & Load to import your newly cleaned data into a new Excel worksheet, perfectly formatted and ready for your PivotTable analysis.
Best regards,
Thank you DataNinja777 to take the time to clearly explain how to proceed. Very appreciated.
