Need help: How to Transform data from combined lists to separate options

Steel Contributor

Jun 27, 2017

Hi Sally,

please find attached my attempt to solve it using Power Query. The query automatically extracts the names of flavours and creates up to 3 flavour columns and 3 years. If you wish to add extra flavours, you would need to add them into the query. Alternatively, you could setup a custom function, which would automatically add flavour columns (and years) based on the number of flavours.

Here is the query, assuming that your initial table is named tblSource

let

    //Create list of flavours
    Source = Excel.CurrentWorkbook(){[Name="tblSource"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Flavour] <> null)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Flavour"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Other Columns"),
    Flavours = #"Removed Duplicates"[Flavour],

    //Variables to hold flavour names
    Flavour1 = try Text.From(Flavours{0}) otherwise "",
    Flavour2 = try Text.From(Flavours{1}) otherwise "",
    Flavour3 = try Text.From(Flavours{2}) otherwise "",

    //Add flavour and year columns
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Flavour", type text}, {"Year", Int64.Type}}),
    #"Added Flavour1" = if Text.Length(Flavour1)>0 then Table.AddColumn(#"Changed Type", Flavour1, each if [Flavour]=Flavour1 then 1 else "") else "",
    #"Added Flavour2" = if Text.Length(Flavour2)>0 then Table.AddColumn(#"Added Flavour1", Flavour2, each if [Flavour]=Flavour2 then 1 else "") else #"Added Flavour1",
    #"Added Flavour3" = if Text.Length(Flavour3)>0 then Table.AddColumn(#"Added Flavour2", Flavour3, each if [Flavour]=Flavour3 then 1 else "") else #"Added Flavour2",
    #"Added Year1" = Table.AddColumn(#"Added Flavour3", "Year 1", each if [Year]=1 then 1 else ""),
    #"Added Year2" = Table.AddColumn(#"Added Year1", "Year 2", each if [Year]=2 then 1 else ""),
    #"Added Year3" = Table.AddColumn(#"Added Year2", "Year 3", each if [Year]=3 then 1 else ""),
    #"Removed Columns" = Table.RemoveColumns(#"Added Year3",{"Flavour", "Year"})
in
    #"Removed Columns"

Hope this helps

Yury

Transform data from combined list.xlsx17 KB

Yury Tokarev
Steel Contributor
Jun 30, 2017
Hi Sally,

you need to use a recursive funciton. Please see the attached file for my attempted solution

Here is the text of the function I used:

let

    /* This function loops the list of headers and adds new columns to the source table, which match the list */
    fnAddColumnsFromList = (nmb_index as number,
   lst_flavours as list,             /*List of column headers*/
    tbl_source as table ) =>          /*initial source table to append columns to*/

    if nmb_index >= List.Count(lst_flavours) /*iterate only the number of times matching the number of items in the list of column headers*/

        then
            tbl_source     /*end result of the function once the maximum number of iteration is achieved*/

        else
            //add new column
            @fnAddColumnsFromList(nmb_index+1,            /*increment index for next iteration*/
                                 lst_flavours,
                                 if Text.Length(try Text.From(lst_flavours{nmb_index}) otherwise "")>0 then Table.AddColumn(tbl_source, Text.From(lst_flavours{nmb_index}), each if [Flavour]=Text.From(lst_flavours{nmb_index}) then 1 else "") else ""
                                 )

in
    fnAddColumnsFromList

Yury
Transform data from combined list.xlsx22 KB
- Sally Lim
  Copper Contributor
  Jul 04, 2017
  Thanks Yury! That query is very helpful and I can manage to replicate it with other data manually. However I was wondering if there's a way to apply this query to a new set of data without having to write up the query again manually? And what would you suggest for multiple columns of data rather than just 2?
  
  Appreciate your help!
  Sally
  - Yury Tokarev
    Steel Contributor
    Jul 12, 2017
    Hi Sally,
    
    could you please give examples of a new set of data you would like to apply the query to, and of the multiple columns of data you woule like to have?
    
    Thank you
    Yury
- Imke Feldmann
  MVP
  Jul 02, 2017
  That's a very smart solution Yury Tokarev!
  My take would have been to pivot twice like this (that can be done completely through the UI):
  
  let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], RowNumber = Table.AddIndexColumn(Source, "RowNumber", 1, 1), ForFirstPivot = Table.AddColumn(RowNumber, "FirstPivot", each 1), ForSecondPivot = Table.AddColumn(ForFirstPivot, "SecondPivot", each 1), PivotFlavour = Table.Pivot(ForSecondPivot, List.Distinct(ForSecondPivot[Flavour]), "Flavour", "FirstPivot"), PivotYear = Table.Pivot(Table.TransformColumnTypes(PivotFlavour, {{"Year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(PivotFlavour, {{"Year", type text}}, "en-US")[Year]), "Year", "SecondPivot") in PivotYear
  - Yury Tokarev
    Steel Contributor
    Jul 12, 2017
    Hi Imke,
    
    that is a great solution.
    
    Thank you
    Yury

Forum Discussion

Need help: How to Transform data from combined lists to separate options