Forum Discussion
PIVOT TABLE AT POWER QUERY
Hello,
I have a table and I need to do a Pivot Table in Power Query:
| City | Region | Code |
| São Paulo | A | 10 |
| São Paulo | B | 20 |
| São Paulo | B | 20 |
| Rio | A | 15 |
| Rio | A | 25 |
| Rio | B | 25 |
| Rio | B | 30 |
| Rio | C | 30 |
| Madrid | C | 11 |
| Barcelona | A | 14 |
| Porto | A | 5 |
| Porto | B | 5 |
| Porto | C | 5 |
| Porto | C | 5 |
I need distinct count the colunm of Code and returns something like this:
Anybody can help me?
Tks.
Consider your table, then select column City and right click on it, then pick group by and make the setting provided in the folowing image.
it results in the following M code
= Table.Group(Source, {"City"}, {{"Count", each List.Sum([Region]), type nullable text}})
rewrite this formula as following
= Table.Group(Source, {"City"}, {{"Count", each List.Count(List.Distinct([Region])), type nullable text}})
which results in the following
so the whole code for your problem is :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCj68OF8hILE0J19JR8kRiA0NlGJ10MSdgNgIv3hQJtwAUzQBI2QBJ2wCxshmOCMEfBNTijJToGKGhmAxp8Si5NSc/LxEmHUmYOGA/KISmIWmKCJOGCLOuERiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Region = _t, Code = _t]),
#"Grouped Rows" = Table.Group(Source, {"City"}, {{"Count", each List.Count(List.Distinct([Region])), type nullable text}})
in
#"Grouped Rows"
2 Replies
- lbendlin
Super User
Not sure why you would want to do that in Power Query?
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCj68OF8hILE0J19JR8kRiA0NlGJ10MSdgNgIv3hQJtwAUzQBI2QBJ2wCxshmOCMEfBNTijJToGKGhmAxp8Si5NSc/LxEmHUmYOGA/KISmIWmKCJOGCLOuERiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Region = _t, Code = _t]), #"Removed Columns" = Table.RemoveColumns(Source,{"Region"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"City"}, {{"Count", each Table.RowCount(Table.Distinct(_)), Int64.Type}}) in #"Grouped Rows" - Omid_Motamedise
Consider your table, then select column City and right click on it, then pick group by and make the setting provided in the folowing image.
it results in the following M code
= Table.Group(Source, {"City"}, {{"Count", each List.Sum([Region]), type nullable text}})
rewrite this formula as following
= Table.Group(Source, {"City"}, {{"Count", each List.Count(List.Distinct([Region])), type nullable text}})
which results in the following
so the whole code for your problem is :
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCj68OF8hILE0J19JR8kRiA0NlGJ10MSdgNgIv3hQJtwAUzQBI2QBJ2wCxshmOCMEfBNTijJToGKGhmAxp8Si5NSc/LxEmHUmYOGA/KISmIWmKCJOGCLOuERiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [City = _t, Region = _t, Code = _t]),
#"Grouped Rows" = Table.Group(Source, {"City"}, {{"Count", each List.Count(List.Distinct([Region])), type nullable text}})
in
#"Grouped Rows"
