Forum Discussion

Copper Contributor

Jun 28, 2017

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

Hi, does anyone know if there's an efficient way to transform columns of data as seen in left image below to a new set that looks like image on the right?

BI & Data Analytics

Need Help

Sally Lim

Copper Contributor

Jun 29, 2017

Thanks so much Yury! :) Do you know what steps are involved with creating it into a custom function as you mentioned above?

Yury Tokarev

Iron Contributor

Jul 01, 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 05, 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
  Iron Contributor
  Jul 13, 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
Iron Contributor
Jul 13, 2017
Hi Imke,

that is a great solution.

Thank you
Yury