Group by columns

[Nicole]

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

[JPotwade]

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.

[HarrisMalik · Accepted solution]

@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.

[mmoizk]

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

Options

4m ago

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

[mmoizk]

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

[Debjit24x7]

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"

[Debjit24x7]

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"

[itchyeyeballs]

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