Custom column isn't in power bi query afterward

[MylèneB]

Hi!

I have a list of names, and if they exist more than once, I would like to add a '2' at the end of their name. So John Doe and John Doe2 for example. I tried to do this manipulation to see where are my duplicates: https://community.powerbi.com/t5/Desktop/Count-duplicate-values-and-number-them-as-1-2-3/td-p/297968 in Power Bi desktop. 

I didn't find a way to do it directly in power bi query editor, so I added the columns in the power bi report. But, when I open the power bi query editor, they don't appear. I would like to replace all the '1' for blank so I can concatenate the names after. 

Why the custom columns added in power bi 'regular' don't appear in my power bi query editor after? Can someone help me?

Thank you!

[camargos88 · Accepted solution]

Hello @MylèneB ,

Mark this file as an example: Download PBIX

Groups the name and assigns an index for it when it is greater than 1.

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

[v-diye-msft · Accepted solution]

Hi @MylèneB 

You can access it directly in power query instead of the report model, please refer to:

https://community.powerbi.com/t5/Desktop/How-to-count-duplicate-values-in-M/td-p/312604 

https://community.powerbi.com/t5/Desktop/Adding-conditional-index-based-on-changing-field-in-Power-Q... 

Below simple example for your ease:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK00HUsKFKK1YFyLVG4Rsaosrq+iZVgnhNI0hAuiYVrbIDCNYFojQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Group     " = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group     ", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group     "}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded Count"

[v-diye-msft · Accepted solution]

Hi @MylèneB 

You can access it directly in power query instead of the report model, please refer to:

https://community.powerbi.com/t5/Desktop/How-to-count-duplicate-values-in-M/td-p/312604 

https://community.powerbi.com/t5/Desktop/Adding-conditional-index-based-on-changing-field-in-Power-Q... 

Below simple example for your ease:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTK00HUsKFKK1YFyLVG4Rsaosrq+iZVgnhNI0hAuiYVrbIDCNYFojQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Group     " = _t, Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Group     ", type text}, {"Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Group     "}, {{"Count", each Table.AddIndexColumn(_,"Index",1,1), type table }}),
    #"Expanded Count" = Table.ExpandTableColumn(#"Grouped Rows", "Count", {"Date", "Index"}, {"Date", "Index"})
in
    #"Expanded Count"

[camargos88 · Accepted solution]

Hello @MylèneB ,

Mark this file as an example: Download PBIX

Groups the name and assigns an index for it when it is greater than 1.

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

[MylèneB]

Hi!

Yes, it does the trick, but how can I group the names column without deleting all my other columns?

Thank you!

[camargos88]

Hi @MylèneB ,

I deleted just to show the results.

When you group all rows by name, you don't lose any data.

If you check the file, after grouping it has a step to expand the table.

Ricardo

[MylèneB]

Thank you @camargos88 ! It worked! 

Do you know however why the columns created in the report doesn't appear in the query editor?

[camargos88 · Accepted solution]

@MylèneB ,

If you create columns using DAX, you don't see them on Query Editor, just the base table or custom transformation made in Power Query.

Ricardo