Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
Date | Categories |
05/07/2017 | Category1 |
06/07/2017 | Category1 |
06/07/2017 | Category2 |
07/07/2017 | |
07/07/2017 | Category1 |
08/07/2017 | Category3 |
09/07/2017 | |
10/07/2017 | Category1 |
10/07/2017 | Category2 |
10/07/2017 | Category3 |
And I need to quantify the categories in each date, which means this:
Date | Category1 | Category2 | Category3 | "Null" |
05/07/2017 | 1 | |||
06/07/2017 | 1 | 1 | ||
07/07/2017 | 1 | 1 | ||
08/07/2017 | 1 | |||
09/07/2017 | 1 | |||
10/07/2017 | 1 | 1 | 1 |
It's simples to illustrate, but could not reach any solved question with similar in my searches.
Thanks for your help!
Regards!
Solved! Go to Solution.
This was cool,
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
Hi leandromaiaf,
It's quite simple. See the image I've attached.
Tell us if works for you.
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?
Sure. Otherwise, I would not have been able to create the table.
Yeah, I mean by using DAX or M languages. The way you created I cannot graph it. Do you understand my point?
Check this out.
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"
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:
This was cool,
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
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]) )
Hope this helps,
SS
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?
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |