How to make column numbering not consecutive over the whole data set but just for equal column names

[Anonymous]

Hello everyone,

I am loading data into my Power Bi file from a folder, using one of the csv files from that folder as example file.

There are multiple duplicated column names in my data, they get renamed as follows:

Let's say I have 3 columns named "helpercolumn" and 3 colums named "datacolumn". Automatically they get renamed from

• helpercolumn, helpercolumn, helpercolumn, datacolumn, datacolumn, datacolumn

to:

• helpercolumn, helpercolumn_1, helpercolumn_2,
  datacolumn, datacolumn_3, datacolumn_4

But I want them to be:

• helpercolumn, helpercolumn_1, helpercolumn_2,
  datacolumn, datacolumn_1, datacolumn_2

I don't know where the renaming happens, maybe that would be a hint. For me it appears to happen automatically.
Where does that happen and/or how can I change that to the way I want it to be?

Thank you!

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

Pls refer the following :

import data:

After transform:

let
    Source = Excel.Workbook(File.Contents("\\filelink\importtest.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Added Index1" = Table.AddIndexColumn(Sheet1_Sheet, "IndexALL", 1, 1, Int64.Type),
    #"Unpivoted Only Selected Columns" = Table.Unpivot(#"Added Index1", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Only Selected Columns", "Index", 1, 1, Int64.Type),
    #"Grouped Rows" = Table.Group(#"Added Index", {"Value"}, {{"all", each _, type table [Attribute=text, Value=any, Index=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all],"Index1",1)),
    #"Expanded Custom1" = Table.ExpandTableColumn(#"Added Custom", "Custom", {"IndexALL", "Attribute", "Value", "Index", "Index1"}, {"Custom.IndexALL", "Custom.Attribute", "Custom.Value", "Custom.Index", "Custom.Index1"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom1",{"all", "Custom.Value", "Custom.Index"}),
    #"Added Custom1" = Table.AddColumn(#"Removed Columns", "newvalue", each if Value.Is([Value],type text)=true then [Value]&"_"&Number.ToText([Custom.Index1]) else [Value]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"Value", "Custom.Index1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each true),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[Custom.Attribute]), "Custom.Attribute", "newvalue"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Custom.IndexALL"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Columns2", [PromoteAllScalars=true])
in
    #"Promoted Headers"

And if you need to add column like the below ,you only need to add in one step:

Adjust the code according your need!

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

Best Regards

Lucien

[Anonymous]

Thank you very much, that does solve my problem.

It took a bit to understand what happend, some serious braing gymnastics ^^
Thank you!

[Anonymous]

Thank you for your answer.
Yes, that's how I do that currently. But it being hundreds of names to change every time makes me wonder if there is an easier, an automated way.

Kind regards

[speedramps]

Go into the query editor

copy your query (as a backup) before making changes

edit the query and click the advance editor.

you can edit the column names in the advance editor.