none
Text Parsing from Excel File RRS feed

  • Question

  • Hi guys

    I have a few thousand templates to process but unfortunately they are a bit inconsistant. For example:

    Sample data:

    Column1 Column2 Column3 Column4 Column5 Column6
    Name: Simon
    Job: Construction D.O.B: 1/1/2015
    Wage: Address: 123 Fake St

    Desired Output:

    Name: Simon
    Job: Construction
    D.O.B: 1/1/2015
    Wage:
    Address: 123 Fake St

    The underlying logic assumes that (1) all questions end in ":" and  (2) responses are the next non-null value to the right of the question, on the same row.  I've tried a number of things with no luck but I'm sure the geniuses here could help!

    Thanks,
    Simon

    Thursday, April 7, 2016 4:06 PM

Answers

  • Hi Simon,

    quick & dirty one here:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
        #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type text}}),
        Check = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1),
        #"Added Custom" = Table.AddColumn(Check, "Custom", each if Text.EndsWith([Value], ":")=false then Check[Value]{[Index.1]-1} else "")
    in
        #"Added Custom"
    won't return empty "Wage" and needs some cleanup

    Imke Feldmann TheBIccountant.com

    • Marked as answer by Simon Nuss Friday, April 8, 2016 4:56 PM
    Thursday, April 7, 2016 8:34 PM
    Moderator

All replies

  • Hi Simon. Are these templates text or Excel files?

    Ehren

    Thursday, April 7, 2016 7:49 PM
    Owner
  • Apologies, in an Excel worksheet.
    Thursday, April 7, 2016 7:57 PM
  • Hi Simon,

    quick & dirty one here:

    let
        Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
        #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1),
        #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
        #"Changed Type" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Value", type text}}),
        Check = Table.AddIndexColumn(#"Changed Type", "Index.1", 0, 1),
        #"Added Custom" = Table.AddColumn(Check, "Custom", each if Text.EndsWith([Value], ":")=false then Check[Value]{[Index.1]-1} else "")
    in
        #"Added Custom"
    won't return empty "Wage" and needs some cleanup

    Imke Feldmann TheBIccountant.com

    • Marked as answer by Simon Nuss Friday, April 8, 2016 4:56 PM
    Thursday, April 7, 2016 8:34 PM
    Moderator
  • Awesome! I'll give it a go shortly.

    Replacing all nulls with 0 should deal with the Wage issue, I think.

    Thursday, April 7, 2016 8:48 PM
  • Thought you might not need it.

    If the null-replacement doesn't work, try creating a "master-data-header"/"list-with-all-fields" that you merge with an outer.


    Imke Feldmann TheBIccountant.com

    Thursday, April 7, 2016 8:52 PM
    Moderator
  • Brilliant solution (once again), thanks Imke!  Worked like a charm. 
    Friday, April 8, 2016 4:56 PM