Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
to:
But I want them to be:
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!
Solved! Go to 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
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
Thank you very much, that does solve my problem.
It took a bit to understand what happend, some serious braing gymnastics ^^
Thank you!
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |