cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Super User
Super User

Re: Help with a table visualization

That's a bit surprising - didn't we just create a table with unique values on the Product Name?

 

The recommendation to normalize your data (split data tables into multiple tables in order to reduce redundant information) would normally only show it's benefitial effects if you create a 1:n-relationship. So maybe you should re-analyze your table structure.

 

Question is, if this is really needed here. Any signs of performance problems already? Otherwise I'd go with one big table.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




mork Member
Member

Re: Help with a table visualization

@ImkeF

We constructed a column with the resources that worked in each project.

 

But that is not my problem now.

I just want to have cleaner tables that are more easy to use.

 

Let me explain once more how my data looks.

 

I have two tables. the first is a project table. that has all th information on my projects. stuff like project name, client name, proposed mandays etc.

 

ProjectName       ProposedMandays

a                            11

b                            34

c                            23

 

 

the second table is constructed from the timesheets my resources fill every week. each row in this table is an entry in these timesheets. For reference it looks like this:

 

ProjectName         ResourceName         TimesheetActualWork         Week

      a                                John                                  8                          1

      a                                John                                  5                          2

      a                                George                             8                          1

      b                                John                                  3                          2

      b                                George                             8                          2

 

 

As you can see this table's data is the actual work from the timesheets, per week per project and per resource so I have multiple rows for the same project/resource/week.

For some reason I can't relate these two tables with project name so I'm forced to copy some columns from the project table to the timesheets table and also the oposite. in order to have all the data I need in the table that I'm using.

I'm constructing visualizations from both tables, I dont have just one main table.

The problem with the way my data looks right now is that it's hard to manipulate. I have some columns that are the same on both tables because I can't make a relationship between the two tables.

 

I tried merging both tables into one but that didnt work well and wasn't convinient because the project table is arranged per project and the timesheet table is arranged per prioject, per week and per resource. Combining the two tables made the final table really hard to work with...

Super User
Super User

Re: Help with a table visualization

OK, so for my understanding you're having a logical 1:n-structure here which totally makes sense. So your aim to reallocate some columns to where they belong in the query-editor should work - also with the technique I've provided.

 

If the problem "for some reason I can't relate these two tables with project name" is a technical one, this might be due to the different handling of case sensitivity in the query editor (M) and the table view (DAX). This might actually cause you trouble with the table we've just created!!

 

If you have Product Names like: "MyProduct1" and "Myproduct1", we'd have created 2 lines in the query editor right now. But the table view would consider them as identical: Therefore seeing 2 rows with the same key in the lookup-table: No connection possible.

 

If that's the case, you must:

1) Decide if both products above are actually the same in your business logic or not

2) If yes: You can consolidate them by lowercasing or UPPERCASING

3) If no: You have to create a new key: http://www.thebiccountant.com/2015/08/17/create-a-dimension-table-with-power-query-avoid-the-bug/

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Super User
Super User

Re: Help with a table visualization

BTW: Could also be caused by blanks or other invisible non-printable characters in your key. Cleaning and trimming will help then. Therefore it's always better to take number-formats for your key columns.

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




mork Member
Member

Re: Help with a table visualization

@ImkeF I managed to create a connection between the two tables and now my data is more clean and simple. I had a blank row in one of the tables that I hadn't noticed.

 

 

A problem that I noticed though is with the code you provided at your first post.

 

let
    Source = Ressource,
    GroupRows = Table.Group(Source, {"ProjectName"}, {{"ResourceName", each _, type table}, {"SumTimesheetActualWork", each List.Sum([#"TimesheetActualWork"]), type number}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "Custom", each Text.Combine([ResourceName][ResourceName], ", ")),
    Cleanup = Table.RemoveColumns(TransformColumnToText,{"ResourceName"})
in
    Cleanup

 

It looks like it's working fine but after using it on visualizations I get the names of my resources mulitiple times in a cell.

 

This is happening because of the format of my table.

 

ProjectName         ResourceName         TimesheetActualWork         Week

      a                                John                                  8                          1

      a                                John                                  5                          2

      a                                George                             8                          1

      b                                John                                  3                          2

      b                                George                              8                          2

 

 

using the code you provided i.e. for project a i get the name John twice. and my final table looks something like this:

 

ProjectName           SumTimesheetActualWork            Resources

       a                                    21                                    John, John, George

       b                                    11                                    John, George

 

 

 

Is there a way to remove duplicates?

Super User
Super User

Re: Help with a table visualization

List.Distinct will do that:

let
    Source = Ressource,
    GroupRows = Table.Group(Source, {"ProjectName"}, {{"ResourceName", each _, type table}, {"SumTimesheetActualWork", each List.Sum([#"TimesheetActualWork"]), type number}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "Custom", each Text.Combine(List.Distinct([ResourceName][ResourceName]), ", ")),
    Cleanup = Table.RemoveColumns(TransformColumnToText,{"ResourceName"})
in
    Cleanup

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

Highlighted
mork Member
Member

Re: Help with a table visualization

@ImkeF Thank you that did the trick!!!

Sean Super Contributor
Super Contributor

Re: Help with a table visualization

@mork I have something very similar to your model

 

When you go to in the Relationship View window you should have

 

Project Table 1 =>>> * Time Sheet Table

 

And when you click on the line connecting the two tables you should see ProjectName field highlighted

(ProjectName should have unique values in the Project table)

 

If this is your set up then @Greg_Deckler formula works if instead of a Calculated Column you create a new Measure

 

Resource List = CONCATENATEX(VALUES(TimeSheetTable[ResourceName]), TimeSheetTable[ResourceName], ", ")

 

Then create a table and use ProjectName field from your Project table and then the Resource List Measure

 

Depending on how many names you have you may have to turn off the table totals

 

Sean Super Contributor
Super Contributor

Re: Help with a table visualization

let
    Source = Ressource,
    GroupRows = Table.Group(Source, {"ProjectName"}, {{"ResourceName", each _, type table}, {"SumTimesheetActualWork", each List.Sum([#"TimesheetActualWork"]), type number}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "Custom", each Text.Combine(List.Distinct([ResourceName][ResourceName]), ", ")),
    Cleanup = Table.RemoveColumns(TransformColumnToText,{"Resour​ceName"})
in
    Cleanup

 

@ImkeF Thanks for the code above - I made it work for my model

 

I'm not familiar with M - is there a way to exclude a certain value "Deleted" from being included in the "Custom" column?

 

EDIT: Also could the results in each row be alphabetized?

 

Thanks!

Super User
Super User

Re: Help with a table visualization

Easiest way would be to add a step before the cleanup that filters out rows containing "Deleted". You can use the UI. Just edit the steps and add a step: Check the Custom-column click on the arrow.

 

I don't understand what you mean with alphabetized: Do you want to convert numbers to text-characters?

 

 

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,479)