Convert table that is using #lf (line feed)

[DanFromMontreal]

Hello dear community,

I was recently given a file to analyze and to my surprise, the way the information is captured does not allow my to properly use a pivot table.

It uses Line Feed within the cell - see my example below to better demonstrate what I mean.

My real table contains 22 columns x 857 rows.  Doing it manually would be insane.

As I am a novice in PowerM, is there an easy way to convert it in PowerM??????

Your help is much appreciated.

Daniel

Cat1	Cat2	Cat3	Unit	qty	Year	%
A	V1	ABC	Sys 1 Sys 2 Sys 3 Sys 4	1 2 1 4	2011 2014 2012 2012	100%
B	V2	ABC	Sys 1 Sys 2 Sys 3	1 4 N/A	2003 2012 N/A	100%
C	V33	CDE	Sys 1 Sys 2	1 N/A	1991 N/A	100%

[p45cal · Accepted solution]

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
    ReorderedColumns

[dufoq3]

Hi @DanFromMontreal, dynamic solution:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUQozBBKOTs5AMriyWMEwJg9EGUEoYwhlApQESgAFgSSIY2RgCOIbGJqASSMICVJlYKCqFKsTreQEMtoIn9EQM4EG+Ok7go00MIYZBhGBmwUyIMwYpMHZxRXNMIgpUA2WloaommMB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Cat1 = _t, Cat2 = _t, Cat3 = _t, Unit = _t, qty = _t, Year = _t, #"%" = _t]),
    Split = Table.TransformColumns(Source, {}, each if _ is text then Text.Split(_, "#(lf)") else {_}),
    Expand = Value.ReplaceType(Table.Combine(Table.AddColumn(Split, "T", each let a = Record.FieldNames(_) in Table.FillDown(Table.FromColumns(Record.ToList(_), a), a))[T]), Value.Type(Table.FirstN(Source, 0)))
in
    Expand

[v-menakakota]

Hi  @DanFromMontreal ,

Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank  @p45cal , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference. 

I hope the below details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you.

Best Regards, 
Community Support Team.

[p45cal · Accepted solution]

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
    ReorderedColumns

[DanFromMontreal]

Hello @p45cal , I have another simple question.

When the table is expanded, the type is set to text.

My column "qty" contain numbers and text (N/A).

I tried to set the column as "type any" but the number remains as a text.

How would I change the query to make the output in the "qty" column to be a number when it is one and text for the rest?

[p45cal · Accepted solution]

You 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.

[DanFromMontreal]

Wow... simple.  Just what I wanted.

Thank you @dufoq3 

[dufoq3]

@DanFromMontreal Did you mean me? You've replied to @p45cal 

[DanFromMontreal]

😅The response was intended to @p45cal .

Novice in PowerM but also novice in using this platform🤣.

Anyway, both of you (@dufoq3, @p45cal ) are great for helping the community.

Regards,

[ronrsnfld]

I would Group the table by Cat1, then process each subTable to split the LF separated values and fill in the nulls.

Custom Function: Rename fnProcessTable

(tbl as table)=>


[colNames=Table.ColumnNames(tbl),
 a=Table.ToColumns(tbl),
 b=List.TransformMany(
     a,
     each _,
     (x,y)=>Text.Split(Text.From(y),"#(lf)")),
 c=Table.FromColumns(b, colNames),
 d=Table.FillDown(c,colNames)
 
     ][d]

Main Function

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cat1", type text}, {"Cat2", type text}, 
        {"Cat3", type text}, {"Unit", type text}, {"qty", type text}, {"Year", type text}, {"%", Percentage.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Cat1"}, {
        {"All", each fnProcessTable(_), type table [Cat1=nullable text, Cat2=nullable text, Cat3=nullable text, Unit=nullable text, qty=nullable text, Year=nullable text, #"%"=nullable number]}}),
    #"Expanded All" = Table.ExpandTableColumn(#"Grouped Rows", "All", {"Cat2", "Cat3", "Unit", "qty", "Year", "%"})
in
    #"Expanded All"

[DataNinja777]

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,

[DanFromMontreal]

Thank you @DataNinja777 to take the time to clearly explain how to proceed.  Very appreciated.