Forum Discussion
Group by columns
Hi,
I have a DB table which has data as the following:
Version Distance Date
5.0 12 2014-02
5.0 10 2014-03
5.2 130 2014-01
5.0 12 2014-02
5.2 130 2014-01
Now I want to have Version | Distance | Date | Count(Distance) grouped by Distance, version, and date, where count(Distance) is the occurrence of each distrance value. The result is
5.0 12 2014-02 2
5.0 10 2014-03 1
5.2 130 2014-01 2
Is it possible to accomplish that in Data Model? Detailed steps is much better.
thanks
-Nicole
Nicole You do not have to do any thing in the data model in order to accomplish this task.
Put all the three columns in a Table visual. Now drag the distance column again and drop it on table visual and chage the aggregation of this column to Count and your task is done.
6 Replies
Nicole You do not have to do any thing in the data model in order to accomplish this task.
Put all the three columns in a Table visual. Now drag the distance column again and drop it on table visual and chage the aggregation of this column to Count and your task is done.
as HarrisMalik mentions you will probably be better to calculate in your analysis rather than build into your model.
However you can use the group by button in the query editor:
Click Edit Queries
Select the query you want to aggreagte
Click Group By
Hi Malik what if the values we are getting is like dimension domain vaules which need to be clean up with group by and then join the ID column back to a Fact table. to report againts it.
In that case this solution won't work. Any idea for that kind of requierement ?
group by a column and the group item to be shown in comma separated in single column
Have to group by a column and have the group items in coma seprated list in a cloum .
I am importing a sharePoint online list data in my powerBI and the data looks like below
ID Name
1 Pen
2 Chair
2 Table
3 Plug
3 Power
3 shocket
4 Board
4 Marker
4 Eraser
4 Clip
This data need to be tranlate to
ID New Column
1 Pen
2 Chair, table
3 Plug, Power, shocket
4 Board, Marker, Eraser, Clip
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Group By"= Table.Group(Source, {"ID"},
{{"Name", each Combiner.CombineTextByDelimiter(", ")(List.Sort([Name])), type text}})
in
#"Group By"
Hello All,
I am facing issue in calculating Percentage value based on aggregation of dimension. Currently it shows incorrect values
Scenario is like this
CY denotes Current Year
PY denotes Previous Year
I want to calculate ((cy-py)/py)*100) as per multiple dimension i.e Gender, AGe Group, Location, State etc.
Please help me to get the solution.
