Help with a table visualization

This is painfully simple :-) : You need to shift one of your closing parenthesis' after the AGENTS:

TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][AGENT])), ", ")),

So the List.Sort-Command didn't stop soon enough. Strange that it didn't error.

Yes, you just squeeze in a List.Sort between Text.Combine and List.Distinct: Text.Combine(List.Sort(List.Distinct.....)))

You have to add another "grouping-round" before:

let
    Source = ProjectQueries,
    GroupAgents = Table.Group(Source, {"JP", "AGENT"}, {{"Count", each Table.RowCount(_), type number}}),
    CountAgent = Table.AddColumn(GroupAgents, "Custom", each [AGENT]&" ("&Text.From([Count])&")"),
    RemoveCols = Table.RemoveColumns(CountAgent,{"AGENT", "Count"}),
    GroupRows = Table.Group(RemoveCols, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][Custom])), ", "))

! Watch the changed code in the last line !

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?

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

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/

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.

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?

ImkeF Thank you that did the trick!!!

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

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!

Thanks!

By alphabetized I mean the names sorted A-Z on each row

Right now they are just output in the order they are found in the source

John, Victor, Bill => Bill, John, Victor

George, Elizabeth => Elizabeth, George

ImkeF Thanks!

When I add List.Sort - the names get sorted in reverese Z-A - AND all spaces and commas disapper between the names

Bill, Victor, Elizabeth => VictorElizabethBill - so they sort but in reverse Z-A and spaces and commas disapper ???

When I remove List.Sort it goes back to => Bill, Victor, Elizabeth

That's probably due to brackets at the wrong place. Could you please share the codeline?

let
    Source = ProjectQueries,
    GroupRows = Table.Group(Source, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Distinct([AGENT][AGENT]), ", ")),
    #"Duplicated Column" = Table.DuplicateColumn(TransformColumnToText, "ALL Agents Working", "Agents Working"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",(", Deleted"),"",Replacer.ReplaceText,{"Agents Working"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Deleted, ","",Replacer.ReplaceText,{"Agents Working"}),
    Cleanup = Table.RemoveColumns(#"Replaced Value1",{"AGENT"})
in
    Cleanup

With the above code I get

If you are wondering about the other code - here's a brief explanation of my reasoning

I Duplicated the Column so I can see the Original next to the result when I gett rid off Deleted

Then I have 3 scenario with the Deleted

1 - when Deleted is first => Deleted, Name, Name, Name

2 - when Deleted is somewhere in between => Name, Deleted, Name, Name

3 - when Deleted is last => Name, Name, Name, Deleted

after playing around with the code for a while I settled on what you see above

it seems to work for all cases for getting rid of Deleted and the comma

I tried to do it in one line with the OR operator || but it work

Here's the code with List.Insert

let
    Source = ProjectQueries,
    GroupRows = Table.Group(Source, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][AGENT]), ", "))),
    #"Duplicated Column" = Table.DuplicateColumn(TransformColumnToText, "ALL Agents Working", "Agents Working"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",(", Deleted"),"",Replacer.ReplaceText,{"Agents Working"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Deleted, ","",Replacer.ReplaceText,{"Agents Working"}),
    Cleanup = Table.RemoveColumns(#"Replaced Value1",{"AGENT"})
in
    Cleanup

And the result

Thanks! That fixed it all!

Hello ImkeF

I am wondering if something else can be added to the code below...

let
    Source = ProjectQueries,
    GroupRows = Table.Group(Source, {"JP"}, {{"AGENT", each _, type table}}),
    TransformColumnToText = Table.AddColumn(GroupRows, "ALL Agents Working", each Text.Combine(List.Sort(List.Distinct([AGENT][AGENT])), ", ")),
    #"Duplicated Column" = Table.DuplicateColumn(TransformColumnToText, "ALL Agents Working", "Agents Working"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",(", Deleted"),"",Replacer.ReplaceText,{"Agents Working"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Deleted, ","",Replacer.ReplaceText,{"Agents Working"}),
    Cleanup = Table.RemoveColumns(#"Replaced Value1",{"AGENT"})
in
    Cleanup

Is there any way to add a count of AGENT for each JP

So result looks like this

JP - Agents Working

1   - Bill (10), Emma (15),

2   - George (5), Victor (10)

3   - etc...

Thanks for your help!

ImkeF Wow! Amazing!

I've been avoiding M far too long - trying to get by with just the UI.

Thank You again!