How to calculate count of each value in a column and split it into new columns?

[saipawar]

Hi

I have data that looks like the below table. Note that there are blanks in 'Content Name'

Published Date	Content Name
1/3/2020	JAN - hybrid installs
2/2/2020	FEB - KPIS
2/23/2020	FEB - SD WAN
2/24/2020
2/25/2020	Not launch related
2/25/2020	Not launch related
4/1/2020	APR - Mimics
7/3/2020	JUL - Hiking
7/18/2020
10/14/2020	OCT - qwerty
10/14/2020	Not launch related

I added a conditional column to bucketise Column Name, here's the output - 

Published Date	Content Name	Content Type
1/3/2020	JAN - hybrid installs	Launch Related
2/2/2020	FEB - KPIS	Launch Related
2/23/2020	FEB - SD WAN	Launch Related
2/24/2020
2/25/2020	Not launch related	Not launch related
2/25/2020	Not launch related	Not launch related
4/1/2020	APR - Mimics	Launch Related
7/3/2020	JUL - Hiking	Launch Related
7/18/2020
10/14/2020	OCT - qwerty	Launch Related
10/14/2020	Not launch related	Not launch related

Now, further I want to calculate the count of "Content Type" and look at it monthly.

Here's the desired output - Note that nulls/blanks are to be ignored but NOT removed from the data

Month (taken from Published date)	Launch Related	Not Launch related
Jan	1	0
Feb	2	2
April	1	0
July	1	0
October	1	1

How can this output be achieved?

Thanks

[Fowmy]

@saipawar 

Hope this is the output you are expecting?

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pdBNC4IwGMDxr/KwszHforNl0YuZqNFBPPgycjRn6ST89o1KMNBTxz37/ce2KEJemzLaFCQHOxEEKWhVcUG4ADcph8uwuxMUKxHSsIF1VVfl3t5yYQZFl9Y0B8obkTDWyLmTtDwrwCdMHpm/Kx3rfbVZL2V18HbBJDV+bWDDxXIn9bzXbiWAfUj9JaPDfzoTa31meb683JGWNJt49WLwV2dH4i29UX6Fca2pWDN7f1qF0j+epBbdCI9f", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Published Date", type date}}),
    #"Inserted Month Name" = Table.AddColumn(#"Changed Type", "Month Name", each Date.MonthName([Published Date]), type text),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Month Name",{"Content Type", "Month Name"}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Removed Other Columns", "Content Type", "Content Type - Copy"),
    #"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Content Type"]), "Content Type", "Content Type - Copy", List.Count)
in
    #"Pivoted Column"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[ImkeF]

Hi @saipawar ,

I would add a column with 0 or 1 and create a sum instead. 

[saipawar]

@ImkeF If I create a column with 0 and 1, how can I split them into 2 different columns later to match the desired output?

[ImkeF]

Hi @saipawar ,

I believe adding 2 columns instead, one for each should be an option.

[AllisonKennedy · Accepted solution]

@saipawar
If you already have the conditional column, then I think your question is more related to desktop, all the Power Query work is complete.

Just create a matrix visualization and put your new Content Type column in Columns, Month in Rows (assuming you have a DimDate table that relates to Published date), and Content Name in Values with a summarization set to Count.

@saipawar UPDATE: Here is my result using the sample data you provided: 

Note the filter on Content Type and the Values summarization is set to 'Count of Content Name'