Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Chrisjr
Helper IV
Helper IV

Query breaks because of changing amount of columns in other files

Hello Communty. 

 

I have made some transformation to an excel file. 

 

I would like to apply these transformations to any other files with about the same strucure. 

 

The issue is that the amount of columns is not always the same. Therefore, in my query, when I move a custom column to the bigining, the query breaks because on the new file, it does not find for example the column 5 (The column 'Column5' of the table wasn't found.)

 

Let's say I have the below dataset on whicj I applied the transformations :

Column1Column2Column3Column4
brand01/0101/0201/03
Xyz500200200
DUM100200100

 

after I create a custom column that seats next to Column4, I move it to the begining.

= Table.ReorderColumns(#"Added Conditional Column",{"Custom", "Column1", "Column2", "Column3", "Column4"})

 

Now if I apply the same query step on the below dataset, my query breaks:

 

Column1Column2Column3Column4Column5
brand01/0101/0201/0301/03
FUM100200200400
RIM200200100500

 

 

How could I solve this issue? 

 

Thanks,

Chris 

1 ACCEPTED SOLUTION

We started with Table.AddColumn, now it's Table.DuplicateColumn. Okay. Try this 

= Table.ReorderColumns(#"Duplicated Column",{"Column1 - Copy"} & List.Difference(Table.ColumnNames(#"Duplicated Column"), {"Column1 - Copy"}))

View solution in original post

7 REPLIES 7
Chrisjr
Helper IV
Helper IV

If someone would find this post and having the same kind of question, I found this video that is very helpfull: 

https://www.youtube.com/watch?v=4qqXho7ndgU

Chrisjr
Helper IV
Helper IV

thanks for the help @AlienSx 

Chrisjr
Helper IV
Helper IV

Hi @AlienSx , thanks for your reply. Do you replace the "source" term by your actual source? whether I replace the term source or I leave it as it is, I get an error. 

If I use the query as it is above, I get Expression.Error: The column 'Name' of the table wasn't found.Details:
Name

 

thanks

the one that was modified by 

#"Added Conditional Column"

@AlienSx 

so this is my step : = Table.ReorderColumns(#"Duplicated Column",{"Column1 - Copy"} & Table.ColumnNames("C:\Users\CC\Drive\ABC\ABC.xlsx")). 

But somehow I get an error

We started with Table.AddColumn, now it's Table.DuplicateColumn. Okay. Try this 

= Table.ReorderColumns(#"Duplicated Column",{"Column1 - Copy"} & List.Difference(Table.ColumnNames(#"Duplicated Column"), {"Column1 - Copy"}))
AlienSx
Super User
Super User

Hi, @Chrisjr 

= Table.ReorderColumns(#"Added Conditional Column",{"Custom"} & Table.ColumnNames(Source))

where Source is your original table. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors