Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

 

 

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

Hi leandromaiaf,

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

 

CountCategories.png

 

Tell us if works for you.

 

Anonymous
Not applicable

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?

 

 

Anonymous
Not applicable

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

Anonymous
Not applicable

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

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"

Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

 

Anonymous
Not applicable

Nice!! God bless you my friend! Great answer.

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

Anonymous
Not applicable

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?

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.