quick bit of help with a pivot

[Anonymous]

My data looks like this:


but I want it to look like this:


For the life of me I can't do it in Edit Queries. 
Cant seem to add a sample .pbix file.

[mussaenda · Accepted solution]

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZZNaxwxDIb/SthzDpK/xnMslIQspAltbyGHzTKkhc0H091A/n22ENqV7NGr24Aev3otyfbc3a2Iz262+5eHaT4LxHV1vrr9viYizsdPXt2fLyKcGCIUEUIx4EQVImU4Rb69vE1PTaZKkOEUMZOLg6mYiSNmBrwvVeQFRuT6Om17ntMps948Hzbz+19k/I+MEJFuuogs8sX0MHcYggxVrEOyWf1csjh9ZnToDNizauj1Zt7+shK1AA0VKMi56gAlASCzDQRCHuR5/PI6/96pOjAA5Bx1ALmLFgiUgUIFHgITSgFMBoYpAipUQQqwDhUpgBTqZugASU3Uu3JQzHCoVli1qQnLU6fD6n1pcvNoh01rgQfTWjKtqXunca4Pur6R7NWj2RKWd2ezsSicrw/Pk4onM67ev3a9LHsbJ7Qe+AtAX57LJq5+f9r9ka2veteuT6B+sSzFL69+Hkf+uAUgAAzmxQS9+7ldH1X+nT7y1Y7HbMbVqW7Xh2jHEwN9O94MUKMP/OkGNvqgPkG+C4fHw5+9JAohIhlEd4w6ImqQemnigAjGGhUQ6mev51SeqR6RkUYg6DRnSKB6qMv3x/S6//c7fiKTHFDyKKXgSTd6oMGTzqWUTag7ngtS0Zb6hNgBBY9zeXKXIE9fgmd37PFEHk9EDqg4PLGnLeoJW1Ly7M6jFMondP8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Reference = _t, Custom = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Reference", type text}, {"Custom", Int64.Type}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Month", each Date.MonthName(_), type text}}),
    #"Extracted First Characters" = Table.TransformColumns(#"Extracted Month Name", {{"Month", each Text.Start(_, 3), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted First Characters", List.Distinct(#"Extracted First Characters"[Month]), "Month", "Custom", List.Sum)
in
    #"Pivoted Column"

[Anonymous · Accepted solution]

A very easy way without having to see the code. In query, just select your month column and click on 'pivot'

Select your column and click on 'pivot'.

[JosefPrakljacic]

Hey @Anonymous ,

if you don't want to do it in Power Query.

1. Create a Calculated Column -> MonthName = FORMAT(YourDateColumn,"MMMM")
2. Then insert a matrix visual
   1. Put the refrence into the row section
   2. Put the MonthName Column into the column section
   3. Put the refrence into the value section
      1. Right Click it and choose "Count" as aggregation method

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

[Anonymous]

Thanks Josef - that would work, however I need it in that format in a data table so I can create additional columns from it i.e. time between each point etc.

Thanks.

[JosefPrakljacic]

Ok then I would create a calculated table -> by using the ADDCOLUMNS Function where I have my months hardcoded and in the expression there is a certain measure.

I just don't understand why you are not using the PowerQuery Editor. It would be so much easier...

BR,

Josef

[Anonymous]

sorry - I don't think I've made it clear.

I want to do it in PowerQuery / Edit Queries - but cannot work out how to do it.

Thanks.

[JosefPrakljacic]

Hey @Anonymous ,

you extract the month information out of your column. I bet you can do that without further explanation 😉

And then you do this (its a diffrent example but the principle is the same)

Explanation

[Anonymous]

thanks again Josef, but still not quite there.

I've uploaded a sample to play with.

https://drive.google.com/open?id=1WKA2405CSdB7WecOEAvJdQ4k2HUkGzuF

[mussaenda]

Or this like what @JosefPrakljacic suggested:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZZNaxwxDIb/SthzDpK/xnMslIQspAltbyGHzTKkhc0H091A/n22ENqV7NGr24Aev3otyfbc3a2Iz262+5eHaT4LxHV1vrr9viYizsdPXt2fLyKcGCIUEUIx4EQVImU4Rb69vE1PTaZKkOEUMZOLg6mYiSNmBrwvVeQFRuT6Om17ntMps948Hzbz+19k/I+MEJFuuogs8sX0MHcYggxVrEOyWf1csjh9ZnToDNizauj1Zt7+shK1AA0VKMi56gAlASCzDQRCHuR5/PI6/96pOjAA5Bx1ALmLFgiUgUIFHgITSgFMBoYpAipUQQqwDhUpgBTqZugASU3Uu3JQzHCoVli1qQnLU6fD6n1pcvNoh01rgQfTWjKtqXunca4Pur6R7NWj2RKWd2ezsSicrw/Pk4onM67ev3a9LHsbJ7Qe+AtAX57LJq5+f9r9ka2veteuT6B+sSzFL69+Hkf+uAUgAAzmxQS9+7ldH1X+nT7y1Y7HbMbVqW7Xh2jHEwN9O94MUKMP/OkGNvqgPkG+C4fHw5+9JAohIhlEd4w6ImqQemnigAjGGhUQ6mev51SeqR6RkUYg6DRnSKB6qMv3x/S6//c7fiKTHFDyKKXgSTd6oMGTzqWUTag7ngtS0Zb6hNgBBY9zeXKXIE9fgmd37PFEHk9EDqg4PLGnLeoJW1Ly7M6jFMondP8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Reference = _t, Custom = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Reference", type text}, {"Custom", Int64.Type}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Month", each Date.MonthName(_), type text}}),
    #"Extracted First Characters" = Table.TransformColumns(#"Extracted Month Name", {{"Month", each Text.Start(_, 3), type text}})
in
    #"Extracted First Characters"

[mussaenda · Accepted solution]

Try this:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZZNaxwxDIb/SthzDpK/xnMslIQspAltbyGHzTKkhc0H091A/n22ENqV7NGr24Aev3otyfbc3a2Iz262+5eHaT4LxHV1vrr9viYizsdPXt2fLyKcGCIUEUIx4EQVImU4Rb69vE1PTaZKkOEUMZOLg6mYiSNmBrwvVeQFRuT6Om17ntMps948Hzbz+19k/I+MEJFuuogs8sX0MHcYggxVrEOyWf1csjh9ZnToDNizauj1Zt7+shK1AA0VKMi56gAlASCzDQRCHuR5/PI6/96pOjAA5Bx1ALmLFgiUgUIFHgITSgFMBoYpAipUQQqwDhUpgBTqZugASU3Uu3JQzHCoVli1qQnLU6fD6n1pcvNoh01rgQfTWjKtqXunca4Pur6R7NWj2RKWd2ezsSicrw/Pk4onM67ev3a9LHsbJ7Qe+AtAX57LJq5+f9r9ka2veteuT6B+sSzFL69+Hkf+uAUgAAzmxQS9+7ldH1X+nT7y1Y7HbMbVqW7Xh2jHEwN9O94MUKMP/OkGNvqgPkG+C4fHw5+9JAohIhlEd4w6ImqQemnigAjGGhUQ6mev51SeqR6RkUYg6DRnSKB6qMv3x/S6//c7fiKTHFDyKKXgSTd6oMGTzqWUTag7ngtS0Zb6hNgBBY9zeXKXIE9fgmd37PFEHk9EDqg4PLGnLeoJW1Ly7M6jFMondP8B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Reference = _t, Custom = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Reference", type text}, {"Custom", Int64.Type}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type", {{"Month", each Date.MonthName(_), type text}}),
    #"Extracted First Characters" = Table.TransformColumns(#"Extracted Month Name", {{"Month", each Text.Start(_, 3), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted First Characters", List.Distinct(#"Extracted First Characters"[Month]), "Month", "Custom", List.Sum)
in
    #"Pivoted Column"

[mussaenda]

Why you cant do it in Power Query?

[Anonymous]

appreciate your feedback, but your comment isn't very helpful.

I've tried all sorts of ways of cutting it, but can't get the data to look like the sample.

Any suggestions?