none
Modify/create a column from stacked data RRS feed

  • Question

  • I wish to do a report of all requests received from customers.  Each request is saved as a text file in the same folder and imports as Filename|Column1|Column2.  Unfortunately, with the exception of the first row, The field name and the field data are in different rows in the same column...what I wish to use as the column header is in the row above the column's data.  There are 17 rows of data from each of the 104 files we currently have.

    Filename         Column1          Column2

    File1               Header1

    File1               Data1

    File1               Header2

    File1               Data2

    File1               Header3

    File1               Data3

     I would like to keep header1 in Column1 and move Data1 to the same row as Header1 in Column2.  So, move the data in every other row in column1 to column 2 and move the data up one row.

    Friday, March 15, 2019 2:34 PM

Answers

  • Or.... one step (for fun) :-))

    let
        Source = Table.FromRecords(
    				List.Transform(
    							Table.Split(Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content], 2)
    							, each Table.First(_) & [Column2 = Table.Last(_)[Column1]]))
    in
        Source

    Saturday, March 16, 2019 11:21 PM
  • You can also try this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // here's your table
        Grouped = Table.Group(Source, {"Filename"}, 
            {{"tab", 
            each Table.FromRows(List.Split(_[Column1], 2)), 
            type table}}),
        Expanded = Table.ExpandTableColumn(Grouped, "tab", {"Column1", "Column2"}, {"Column1", "Column2"})
    in
        Expanded

    :)


    Saturday, March 16, 2019 7:54 AM
  • Hi Layard,

    You may use following pattern:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        group = Table.Group(Source, {"Filename"}, {"temp", each Table.FromColumns({
                                                           List.Alternate(List.Distinct([Column1]), 1,1,1),
                                                           List.Alternate(List.Distinct([Column1]), 1,1)
                                                           })}),
        expand = Table.ExpandTableColumn(group, "temp", {"Column1", "Column2"})
    in
        expand


    Friday, March 15, 2019 4:12 PM

All replies

  • Hi Layard,

    You may use following pattern:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table"]}[Content],
        group = Table.Group(Source, {"Filename"}, {"temp", each Table.FromColumns({
                                                           List.Alternate(List.Distinct([Column1]), 1,1,1),
                                                           List.Alternate(List.Distinct([Column1]), 1,1)
                                                           })}),
        expand = Table.ExpandTableColumn(group, "temp", {"Column1", "Column2"})
    in
        expand


    Friday, March 15, 2019 4:12 PM
  • You can also try this:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], // here's your table
        Grouped = Table.Group(Source, {"Filename"}, 
            {{"tab", 
            each Table.FromRows(List.Split(_[Column1], 2)), 
            type table}}),
        Expanded = Table.ExpandTableColumn(Grouped, "tab", {"Column1", "Column2"}, {"Column1", "Column2"})
    in
        Expanded

    :)


    Saturday, March 16, 2019 7:54 AM
  • Or.... one step (for fun) :-))

    let
        Source = Table.FromRecords(
    				List.Transform(
    							Table.Split(Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content], 2)
    							, each Table.First(_) & [Column2 = Table.Last(_)[Column1]]))
    in
        Source

    Saturday, March 16, 2019 11:21 PM
  • Bill's ingenious solution is best if you're using a fairly recent version of Office 365 that includes the Table.Split function. A let..in block isn't even necessary in this case.
    Sunday, March 17, 2019 5:01 PM
  • Maxim, Bill, thank you for great solutions! Didn't aware, functions List.Split, Table.Split are exist.
    Monday, March 18, 2019 8:59 AM