none
Add table header as column in rows below it for text files full of tables RRS feed

  • Question

  • I have a text file with series of transaction tables that have identifying header laid out similarly to the example below and then are tab separted rows under them.  Is there a way to add the label at the top as a column in the data below it so that you could have Apple View 1 $10....  Orange View 1 $15?

    Apple

    View 1 $10

    View 2 $25

    View 3 $32

    View 4 $12

    View 5 $39

    View 6 $29

    Orange

    View 1 $12

    View 2 $39

    View 3 $18

    View 4 $12

    View 5 $15

    View 6 $129

    Lemon

    View 1 $18

    View 2 $18

    View 3 $129


    Monday, June 22, 2015 4:25 PM

Answers

  • Hi Grace,

    you add a colum: if [YourValueColumn] is blank then [YourFirstColumn) else null

    then you fill down in your new colum - this will bring the header text beneath your values


    Imke

    • Marked as answer by Grace155 Monday, June 22, 2015 9:02 PM
    Monday, June 22, 2015 4:33 PM
    Moderator

All replies

  • Hi Grace,

    you add a colum: if [YourValueColumn] is blank then [YourFirstColumn) else null

    then you fill down in your new colum - this will bring the header text beneath your values


    Imke

    • Marked as answer by Grace155 Monday, June 22, 2015 9:02 PM
    Monday, June 22, 2015 4:33 PM
    Moderator
  • You may also need to remove the rows where say Apple is in your first column and the custom one you added.

     #"Added Custom" = Table.AddColumn(Sheet2_Sheet, "Custom", each if ([Column2] = null) then [Column1] else (null)),
        #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
     #"DeleteRows" = Table.SelectRows(#"Filled Down", each [Custom] <> [Column1] )

    Monday, June 22, 2015 8:15 PM
  • You can accomplish this task almost entirely through the user interface. I've copied your sample data to a csv file called "Fruit Data."

    After importing your file and performing some transformations, I ended up with the following script:

    let
        Source = Csv.Document(File.Contents("C:\Users\Colin\Documents\My Received Files\Fruit Data.csv"),[Delimiter=",",Encoding=1252]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
        #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if Text.Contains([Column1],"$") then null else [Column1]),
        #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
        #"Added Custom1" = Table.AddColumn(#"Filled Down", "Custom.1", each [Custom]&" "&[Column1]),
        #"Filtered Rows" = Table.SelectRows(#"Added Custom1", each (Text.Contains([Custom.1],"$"))),
        #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Column1", "Custom"})
    in
       #"Removed Columns"

    The only manual modification I made was in the step named "#"Filtered Rows." In that step, I just selected one value in the rightmost column to filter out, and modified the step with highlighted function. To understand the transformations, just view the results of each step in the editor.

    After importing your source file, copy all of the above script after the source step and paste it in the Advanced Editor.

    Monday, June 22, 2015 8:25 PM