cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leandromaiaf Frequent Visitor
Frequent Visitor

Create Counter for Each Value in a Specific Columns as New Columns

Hi community,

 

I stucking in a problem for 2 days long. I searched for examples here but I don't even know the correct words to express it properly.

 

I made these two tabels to show my need. I have a table like so:

 

DateCategories
05/07/2017Category1
06/07/2017Category1
06/07/2017Category2
07/07/2017 
07/07/2017Category1
08/07/2017Category3
09/07/2017 
10/07/2017Category1
10/07/2017Category2
10/07/2017Category3

 

And I need to quantify the categories in each date, which means this:

 

DateCategory1Category2Category3"Null"
05/07/20171   
06/07/201711  
07/07/20171  1
08/07/2017  1 
09/07/2017   1
10/07/2017111 

 

It's simples to illustrate, but could not reach any solved question with similar in my searches.

 

Thanks for your help!

Regards!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Create Counter for Each Value in a Specific Columns as New Columns

This was cool,

 

Possible2.png

 

You should to pivot the Category column.
This is the new script:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDXNzIwNFfSUXJOLElNzy+qNFSK1QFKmZEmZQSRMkeSwhRBM8cCi5QxRMoS3RxDA5zmYJUywi0FtCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Categories = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Categories", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","","""Null""",Replacer.ReplaceValue,{"Categories"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Replaced Value", "Categories", "Categories - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Categories - Copy"]), "Categories - Copy", "Categories", List.Count)
in
#"Pivoted Column"

 

I deserve a big congratulation, so I congratulate myself ... Well done ...; D

 

 

10 REPLIES 10
Anonymous
Not applicable

Re: Create Counter for Each Value in a Specific Columns as New Columns

Hi leandromaiaf,

It's quite simple. See the image I've attached.

 

CountCategories.png

 

Tell us if works for you.

 

leandromaiaf Frequent Visitor
Frequent Visitor

Re: Create Counter for Each Value in a Specific Columns as New Columns

Thanks for you help!

 

Yes, thats exactly what I need, but is it possible to manipulate it in query editor?

 

Or maybe create a new table in the report itself?

 

 

BobBI Member
Member

Re: Create Counter for Each Value in a Specific Columns as New Columns

i created 2 duplicate entries to show the result. Create a summary table and use it to create visual.

 

SUMMARIZE(categories,
          categories[Date],
          categories[Categories],
          "Count",count(categories[Date])
          )

 

stable.JPG

Hope this helps,

SS

leandromaiaf Frequent Visitor
Frequent Visitor

Re: Create Counter for Each Value in a Specific Columns as New Columns

Thanks BobBI.

 

I need to get it in separate columns because I want them to composte an cumulative line chart, in order to access specific category by filtering, I think your solution could not accomplish that.

 

Is there anyway to break it in columns?

Anonymous
Not applicable

Re: Create Counter for Each Value in a Specific Columns as New Columns

Sure. Otherwise, I would not have been able to create the table.

leandromaiaf Frequent Visitor
Frequent Visitor

Re: Create Counter for Each Value in a Specific Columns as New Columns

Yeah, I mean by using DAX or M languages. The way you created I cannot graph it. Do you understand my point?

Anonymous
Not applicable

Re: Create Counter for Each Value in a Specific Columns as New Columns

Check this out.

 

Possible.png 

 

This is the script to load your data into PBI. Change a blanck query advanced editor with it.

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDXNzIwNFfSUXJOLElNzy+qNFSK1QFKmZEmZQSRMkeSwhRBM8cCi5QxRMoS3RxDA5zmYJUywi0FtCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Categories = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Categories", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","","""Null""",Replacer.ReplaceValue,{"Categories"})
in
#"Replaced Value"

leandromaiaf Frequent Visitor
Frequent Visitor

Re: Create Counter for Each Value in a Specific Columns as New Columns

Thanks, Miltinho.

 

I need the data to be separated in columns, otherwise I will lose the timeline. 

 

My goal is to obtain a graph like so:

 

1.png

Highlighted
Anonymous
Not applicable

Re: Create Counter for Each Value in a Specific Columns as New Columns

This was cool,

 

Possible2.png

 

You should to pivot the Category column.
This is the new script:

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDXNzIwNFfSUXJOLElNzy+qNFSK1QFKmZEmZQSRMkeSwhRBM8cCi5QxRMoS3RxDA5zmYJUywi0FtCIWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t, Categories = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Categories", type text}}),
#"Replaced Value" = Table.ReplaceValue(#"Changed Type","","""Null""",Replacer.ReplaceValue,{"Categories"}),
#"Duplicated Column" = Table.DuplicateColumn(#"Replaced Value", "Categories", "Categories - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Categories - Copy"]), "Categories - Copy", "Categories", List.Count)
in
#"Pivoted Column"

 

I deserve a big congratulation, so I congratulate myself ... Well done ...; D