Forum Discussion

jcmartin01's avatar
jcmartin01
New Member
3 years ago
Solved

Pivot - Several lines for the same pivot.

Hello everybody 😉

I try to pivot this table :

 

QMNUM - MNCOD - ERLDAT
207514 - XXX - 08/11/2010
207515 - XXX - 15/04/2011
207515 - XXX - 30/08/2011
207516 - XXX - 15/09/2011

 

but I get an error, probably because I have several lines for 207517

 

QMNUM - XXX
207514 - 08/11/2010
207515 - Error
207516 - 15/09/2011

 

My pivot :

= Table.Pivot(

Table.TransformColumnTypes(ref, {{"MNCOD", type text}}, "fr-FR"),

List.Distinct(Table.TransformColumnTypes(ref, {{"MNCOD", type text}}, "fr-FR")[MNCOD]), "MNCOD", "ERLDAT"

)

 

Thanks all...

JCM

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi jcmartin01 ,

     

    Select 'Don't' Aggregate' in the advanced options. The error will disappear.

    Testing M codes:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNzU0UdJRioiIAJJGBoYGuoaGuhZKsTpQSVNkSUNdE12gCC5JC11jA4SkGaqkJVhnLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QMNUM = _t, MNCOD = _t, ERLDAT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"QMNUM", Int64.Type}, {"MNCOD", type text}, {"ERLDAT", type date}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[MNCOD]), "MNCOD", "QMNUM")
in
    #"Pivoted Column"

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

3 Replies

  • jcmartin01's avatar
    jcmartin01
    New Member
    3 years ago

    I should have added : does anybody know how to fix this ?

    Id like to see a List of values instead of an error....

    Is this possible ?

    Thanks again !

  • HotChilli's avatar
    HotChilli
    Community Champion
    3 years ago

    If you simplify that Pivot line to something like:

    Table.Pivot(#"Renamed Columns", List.Distinct(thePreviousStepName[MNCOD]), "MNCOD", "ERLDAT", each _)

    That will make the items in the last column into a list.

    You can then decide what you want the last column to do (and you can get power query to write the M for you) by expanding from the column header.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi jcmartin01 ,

     

    Select 'Don't' Aggregate' in the advanced options. The error will disappear.

    Testing M codes:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwNzU0UdJRioiIAJJGBoYGuoaGuhZKsTpQSVNkSUNdE12gCC5JC11jA4SkGaqkJVhnLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [QMNUM = _t, MNCOD = _t, ERLDAT = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"QMNUM", Int64.Type}, {"MNCOD", type text}, {"ERLDAT", type date}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type", List.Distinct(#"Changed Type"[MNCOD]), "MNCOD", "QMNUM")
in
    #"Pivoted Column"

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.