using a primary key as the grain for a text column

[vanessafvg]

I have in my data duplicated names but with different account numbers.  But when i aggregate on name because the account number doesn't make sense to see in the report it obviously aggregates at the name level, how can i aggregate the data at the correct level, is there a way of using the key (account number) with out actually displaying the key?   There is a one to one mapping between account and name, but sometimes there are more than account with the same name, but they are different accounts.  So when you aggregate on name it ignores that its a different account.

i was thinking maybe sort by column would work but it doesn't

[MarcelBeug]

Reading your question, I understand until "how can i aggregate the data at the correct level".

I understood that aggregation by name was the correct level, so please specify "correct level" (maybe illustrated with some fake example data).

[vanessafvg]

name        acc no 

jack black  123

jack black  234

same name but different account. 

[MarcelBeug]

That part was clear already, but how should the resut look like?

If you only need "jack black" once, then you can remove duplicates on the "name" column.

[vanessafvg]

no i need jack black 2x, right now its only showing as once.

in SSAS MDX you can set the id of a of an attribute to the key, and it would display correctly but i have no idea of how to do that in power bi.

[MarcelBeug]

After rereading your question about 500 times, I'm starting to suspect that the issue might be related to a visual?

[vanessafvg]

 yes lol, not in the source data but when you aggregate.  

[MarcelBeug]

What do you mean with "when you aggregate"?

You can aggregate in Power Query, in DAX in visuals.

If you don't become clear with your requirements, I won't be able to help you and I'm not going to spend more time playing games.

Maybe I can't help you at all if it is not in the Power Qyery area, as I first suspected.

[vanessafvg]

Where do you get the impression that i am playing a game here? @MarcelBeug - i have explained this as best i can but let me try and detail it more articulately.

I stated that i am working in the visual, i have not mentioned power query.  When you use a name (column value) and a measure, in a visual it aggregates up to the name, which is the expected behaviour, however what i also mentioned is that in analysis services mdx in these scenarios you can set a key (in this case the acc number) to control the behavior of your name value and I was wondering if you can do that in Power BI.  Maybe you dont understand what I am asking, have you worked in SSAS MDX?

As far asi know this isn't about power query, but maybe there is a setting in power query i dont know, its about controlling how you see the column value in the front end which is also not specifically related to the visual but more to do with  how to use the account number as the key for the name value so that you can display all the accounts in this scenario correctly.  Its a similar but different concept to sorting a value by a different column.

I dont know how else to explain this.

[MarcelBeug]

Well, we seem to have a different sense of logic, but that can happen on an international forum, so fair enough and apologies.

Now I do have a solution for you with some complex Power Query code.

It creates a new column "names", in which "delete" characters are added for every different account of each person, so that would look like (if it would be visible):

123 jack black

234 jack black"delete"

234 jack black"delete"

456 jack black"delete""delete""delete"

Why the "delete" character (character number 127)?

This is the only ASCII character that is treated differently by Excel versus Power Query when it comes to cleaning text:

Excel function CLEAN doesn't remove this character, while Text.Clean in Power Query does remove this character.

So I guessed this might be considered different values for the visual and that's exactly what is happening.

Now the code with example data you can copy paste and then create a table visual with the field "names" and you will have your duplicates:

let
    Source = #table(type table[name = text, acc no = number],{{"jack black",123},{"jack black",234},{"jack black",234},{"john  doe",234}}),
    OriginalSort = Table.AddIndexColumn(Source, "OriginalSort", 0, 1),
    Grouped = Table.Group(OriginalSort, {"name"}, {{"AllData", each _, type table}}),
    AddedSpecChars = Table.TransformColumns(Grouped,{{"AllData", (This) =>
    let
        Grouped = Table.Group(This, {"acc no"}, {{"AllData", each _, type table}}),
        AddedSpecChars = Table.TransformColumns(Grouped,{{"AllData", (This) =>
        let
            Indexed = Table.AddIndexColumn(This, "Index", 0, 1),
            AddSpecChars = Table.AddColumn(Indexed, "names", each [name] & Text.Repeat(Character.FromNumber(127),[Index]))
        in
            AddSpecChars
            }}),
        Expanded = Table.ExpandTableColumn(AddedSpecChars, "AllData", {"names"}, {"names"})
    in
        AddedSpecChars
        }}),
    Expanded1 = Table.ExpandTableColumn(AddedSpecChars, "AllData", {"acc no", "AllData"}, {"acc no", "AllData.1"}),
    Expanded2 = Table.ExpandTableColumn(Expanded1, "AllData.1", {"OriginalSort", "names"}, {"OriginalSort", "names"}),
    SortedBack = Table.Sort(Expanded2,{{"OriginalSort", Order.Ascending}}),
    RemovedSort = Table.RemoveColumns(SortedBack,{"OriginalSort"})
in
    RemovedSort

[vanessafvg]

no problem i understand misunderstandings happen easily in this medium , that looks interesting, thank you i will check it out!