none
Power Query Error While Pivot RRS feed

  • Question

  • Hi All,

    I am using below code for Pivoting my data and its working fine for maximum cases, but few cases its showing error as below snapshot.

    Basically My column name :-

    ND000   Technology  : Contains data in 3 category like 2G/3G/4G

    and error is coming because in column ND000 Unique Site (OC) there are more than 1 cases for 2G/3G/4G.

    I want if there more than 1 cases for any of 2G/3G/4G than it needs to show in new row.

    Could you please suggest what modification I need to done in below code to avoid this error.

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ND000 Unique Site (OC)", type text}, {"Node", type text}}),
        #"Sorted Rows" = Table.Sort(#"Changed Type",{{"ND000 Unique Site (OC)", Order.Ascending}}),
        #"Filtered Rows" = Table.SelectRows(#"Sorted Rows", each ([#"ND000 Unique Site (OC)"] <> null)),
        #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[#"ND000 Technology"]), "ND000 Technology", "Node")
    in
        #"Pivoted Column"


    Rajender

    Tuesday, July 17, 2018 11:29 AM

Answers

  • Hi Rajender,

    actually, you have to create a nested Index to achieve your result like so:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Node", type text}, {"ND000 Unique   Site (OC)", type text}, {"ND000 Technology", type text}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"ND000 Unique   Site (OC)", "ND000 Technology"}, {{"Partition", each Table.AddIndexColumn(_, "Index",0,1), type table}}),
        #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Node", "Index"}, {"Node", "Index"}),
        #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
        #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[#"ND000 Technology"]), "ND000 Technology", "Node")
    in
        #"Pivoted Column"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Sunday, July 29, 2018 8:48 AM
    Moderator

All replies

  • Hi Rajender. I'm not 100% sure what you're asking. Can you provide a small sample of Table1's content so I could reproduce the results you're seeing?

    Ehren

    Thursday, July 19, 2018 12:15 AM
    Owner
  • Hi Ehren,

    Below is my sample data :-

    Node ND000 Unique   Site (OC) ND000 Technology
    1A VB_000_725 2G
    1B VB_000_725 3G
    1C VB_000_725 4G
    11A VB_000_725 2G
    2A LX_000_346 2G
    2B LX_000_346 3G
    2C LX_000_346 4G
    3A AN_001_240 2G
    3B AN_001_240 3G
    3C AN_001_240 4G
    3D AN_001_240 3G
    3E AN_001_240 4G
    4A AN_001_245 4G

    and Output sampIe 1 is needed in below format :-

    ND000   Unique Site (OC) 2G 3G 4G
    VB_000_725 1A 1B 1C
    VB_000_725 11A    
    LX_000_346 2A 2B 2C
    AN_001_240 3A 3B 3C
    AN_001_240   3D  
    AN_001_245     4A

    Output sample 2 in below format :-

    ND000   Unique Site (OC) 2G 3G 4G
    VB_000_725 1A, 11A 1B 1C
    LX_000_346 2A 2B 2C
    AN_001_240 3A 3B, 3D 3C
    AN_001_245     4A

    Thank you for your valuable support.


    Rajender

    Thursday, July 19, 2018 4:28 AM
  • Hi Rajender

    Add an Index Column to the table before pivoting. With index Pivot will place the each value of Node to the separate row


    Maxim Zelensky Excel Inside

    Thursday, July 19, 2018 2:38 PM
  • Hi Maxim,

    Thanks a lot for your valuable suggestion. I am able to get the result output format 2. but still for output sample 1, I am unable to solve. Please assist.

    Sample data.

    Node ND000 Unique   Site (OC) ND000 Technology
    1A VB_000_725 2G
    1B VB_000_725 3G
    1C VB_000_725 4G
    11A VB_000_725 2G
    2A LX_000_346 2G
    2B LX_000_346 3G
    2C LX_000_346 4G
    3A AN_001_240 2G
    3B AN_001_240 3G
    3C AN_001_240 4G
    3D AN_001_240 3G
    3E AN_001_240 4G
    4A AN_001_245 4G

    and Output sampIe 1 is needed in below format :-

    ND000   Unique Site (OC) 2G 3G 4G
    VB_000_725 1A 1B 1C
    VB_000_725 11A    
    LX_000_346 2A 2B 2C
    AN_001_240 3A 3B 3C
    AN_001_240   3D  
    AN_001_245     4A


    Rajender

    Friday, July 20, 2018 6:15 PM
  • Hi Rajender,

    actually, you have to create a nested Index to achieve your result like so:

    let
        Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"Node", type text}, {"ND000 Unique   Site (OC)", type text}, {"ND000 Technology", type text}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"ND000 Unique   Site (OC)", "ND000 Technology"}, {{"Partition", each Table.AddIndexColumn(_, "Index",0,1), type table}}),
        #"Expanded Partition" = Table.ExpandTableColumn(#"Grouped Rows", "Partition", {"Node", "Index"}, {"Node", "Index"}),
        #"Sorted Rows" = Table.Sort(#"Expanded Partition",{{"Index", Order.Ascending}}),
        #"Pivoted Column" = Table.Pivot(#"Sorted Rows", List.Distinct(#"Sorted Rows"[#"ND000 Technology"]), "ND000 Technology", "Node")
    in
        #"Pivoted Column"


    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!


    Sunday, July 29, 2018 8:48 AM
    Moderator
  • Hi Imke,

    Thanks a lot for your valuable support.

    I didn't understand the below code step ?

    Did you customize the code ?

    Could you please suggest.

    #"Grouped Rows" = Table.Group(#"Changed Type", {"ND000 Unique   Site (OC)", "ND000 Technology"}, {{"Partition", each Table.AddIndexColumn(_, "Index",0,1), type table}}),

    Regards,

    Rajender


    Rajender

    Monday, July 30, 2018 4:22 PM
  • That's a clustered (or nested) index: https://goo.gl/D8Bc1L

    Imke Feldmann - MVP Data Platform (PowerBI) - http://www.TheBIccountant.com 

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful - Thanks!

    Tuesday, July 31, 2018 1:29 PM
    Moderator