none
Transformation of table RRS feed

  • Question

  • Hi guys,

    I'm trying to transform following structure:

    Product          Status

    1000001          A

    1000002          A

    1000002          B

    1000003          A

    1000003          B

    1000003          C

    into the following structure:

    Product          A          B          C

    1000001          1

    1000002          1          1

    1000003          1          1           1

    Do you have an idea how I can do it ?

    Thanks a lot in advance for your help.


    • Edited by Branis Thursday, December 7, 2017 8:46 AM
    Thursday, December 7, 2017 8:32 AM

Answers

  • Hi Brains,

    This is very simple transformation. You can do that using UI only.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product   ", Int64.Type}, {"Status", type text}}),
        #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Status", "Status — copy"),
        #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Status]), "Status", "Status — copy", List.Count)
    in
        #"Pivoted Column"
    Cheers

    Thursday, December 7, 2017 9:53 AM
  • Pivot on the Status column. The menu option will only let you choose the Product column as the values column, but you can adjust the generated code afterwards to:

    = Table.Pivot(PreviousStep, List.Distinct(PreviousStep[Status]), "Status", "Status", List.Count)

    Where PreviouStep is the name of your previous step.

    Thursday, December 7, 2017 9:58 AM

All replies

  • Hi Brains,

    This is very simple transformation. You can do that using UI only.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product   ", Int64.Type}, {"Status", type text}}),
        #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Status", "Status — copy"),
        #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[Status]), "Status", "Status — copy", List.Count)
    in
        #"Pivoted Column"
    Cheers

    Thursday, December 7, 2017 9:53 AM
  • Pivot on the Status column. The menu option will only let you choose the Product column as the values column, but you can adjust the generated code afterwards to:

    = Table.Pivot(PreviousStep, List.Distinct(PreviousStep[Status]), "Status", "Status", List.Count)

    Where PreviouStep is the name of your previous step.

    Thursday, December 7, 2017 9:58 AM
  • "Hi Brains,"

    Lol!

    Thursday, December 7, 2017 8:27 PM