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.

     

dufoq3's avatar
dufoq3
Community Champion
10 months ago

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