Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi All,
I have a table below with a list of toys and I'd like to change column A to look like Column D and Column E.
Essentially counting how many times each item appears in column A and then creating a new column that counts how many times the toy appears and then removes rows so were left with a tidy table.
Appreicate any help with this.
Thanks.
Solved! Go to Solution.
@PowerBINoob12 - try this transformation step.
PBIX FILE or else copy the advanced editor -
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk4sUorVwUYXl+SkojNT8nNyUBk5qen5OBgl+ZVKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Type", "Type - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Type - Copy"]), "Type - Copy", "Type", List.Count),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {}, "Attribute", "Value")
in
#"Unpivoted Columns"
-
it should work.
If this post help, pls consider Accept it as the solution. Thanks
Hi @PowerBINoob12 ,
in this case you don't need to create a new column.
You just need to use a table visual with the 2 times the column [Type]: one time as category, second time as count like the n the below example.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I actually need this done on the backend so that I can merge the count of toys table to another table that contains a list of all toys. Thanks though
@PowerBINoob12 - try this transformation step.
PBIX FILE or else copy the advanced editor -
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSk4sUorVwUYXl+SkojNT8nNyUBk5qen5OBgl+ZVKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}}),
#"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Type", "Type - Copy"),
#"Pivoted Column" = Table.Pivot(#"Duplicated Column", List.Distinct(#"Duplicated Column"[#"Type - Copy"]), "Type - Copy", "Type", List.Count),
#"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Pivoted Column", {}, "Attribute", "Value")
in
#"Unpivoted Columns"
-
it should work.
If this post help, pls consider Accept it as the solution. Thanks
Thank you so much - attaching the PBIX file really really helped.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |