Forum Discussion

DanFromMontreal's avatar
DanFromMontreal
Helper IV
10 months ago
Solved

Convert table that is using #lf (line feed)

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 ce...
Need Help
  • p45cal's avatar
    p45cal
    10 months ago

    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

     

  • p45cal's avatar
    p45cal
    10 months ago

    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.

     

ronrsnfld's avatar
ronrsnfld
Super User
10 months ago

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"