cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
heitorleaof Frequent Visitor
Frequent Visitor

Power Query - Change Type from a range os columns by position

Hello everyone!

 

I´m trying to develop a personalized step to change the type of some columns in my table on Power Query that have the following structure (Table below).
However, this code will be an tool that will be apply to another data that can create more columns at the right of the table. So, in a future work, the number of columns can increase.

 

I would like to change the type from the "Column 3" until the end of the table. How can I code some step that can do that even that the numbers of columns increase?

 

The column 1 and column 2 must not change their type values.

Column 1Column 2Column 3Column 4Column 5Column 6Column 7
Name 1Name 16.803.696.652.541.98
Name 2Name 20.691.220.800.720.11
Name 3Name 36.449.521.767.791.17
Name 4Name 42.563.030.483.730.58

 

Thanks for any help.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Power Query - Change Type from a range os columns by position

The function is also very simple, as it takes just one parameter (a table) as it's input. So you would adjust your query to this:

 

let
    Source = Table.Combine({#"fUsage-Peak (REF)", #"fUsage_Off-Peak (REF)", #"fCIP (REF)", #"fTotal Cost (REF)", #"fExcedente Energia Reativa P (REF)", #"fExcedente Energia Reativa FP (REF)", #"fDemand Contracted Off-Peak (VERDE) (REF)", #"fDemand Registered Peak (Actual Null) (REF)", #"fDemand Contracted Peak (VERDE) (REF)", #"fDemand Registered Off Peak (Actual Null) (REF)", #"fDemand Registered Peak (Dem Peak) (REF)", #"fDemand Registered Off Peak (Dem Peak) (REF)", #"fDemanda Contracted Peak (AZUL) (REF)", #"fDemanda Contracted Off Peak (AZUL) (REF)", #"fReactive Demand Peak (REF)", #"fReactive Demand Off Peak (REF)"}),
    ApplyFunction = FunctionName(Source)
in ApplyFunction

Where "FunctionName" is the name of the query that holds the function code that you have copied.

 

Please see attached file.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

6 REPLIES 6
Super User
Super User

Re: Power Query - Change Type from a range os columns by position

This function automatically transforms each column to the type of the value it finds in the first row: https://www.thebiccountant.com/2017/08/10/table-transform-column-types-to-first-rows-types/

Please check if it works for you.

 

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




heitorleaof Frequent Visitor
Frequent Visitor

Re: Power Query - Change Type from a range os columns by position

Sorry, but I couldn´t use the function because I don´t know how to use the reference of the "(Table)=>" above the code.

I tried to modify the code, but it didn´t work out.

 

Can you tell me more about how to use this function using a simple example?

 

Thank you!

Super User
Super User

Re: Power Query - Change Type from a range os columns by position

Please watch this video where you can see how to use custom functions in Power Query: https://www.youtube.com/watch?v=6TQN6KPG74Q

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




heitorleaof Frequent Visitor
Frequent Visitor

Re: Power Query - Change Type from a range os columns by position

Unfortunally, I still don´t get it how to use the code. Look, my table so far have the following columns. The columns 1 to 6 always will be in that order. However, from column 7 to final the number of columns can change depending of data range I´ll use. 

 

I would like to set the type from column 7 until the end to "Decimal type". I´m trying to use the code that you´ve sent to me, but I´m still having problem with the first part (red circle on image written "Table").

 

My code it´s simple because this table it´s an append of others. This is the code.

let
    Source = Table.Combine({#"fUsage-Peak (REF)", #"fUsage_Off-Peak (REF)", #"fCIP (REF)", #"fTotal Cost (REF)", #"fExcedente Energia Reativa P (REF)", #"fExcedente Energia Reativa FP (REF)", #"fDemand Contracted Off-Peak (VERDE) (REF)", #"fDemand Registered Peak (Actual Null) (REF)", #"fDemand Contracted Peak (VERDE) (REF)", #"fDemand Registered Off Peak (Actual Null) (REF)", #"fDemand Registered Peak (Dem Peak) (REF)", #"fDemand Registered Off Peak (Dem Peak) (REF)", #"fDemanda Contracted Peak (AZUL) (REF)", #"fDemanda Contracted Off Peak (AZUL) (REF)", #"fReactive Demand Peak (REF)", #"fReactive Demand Off Peak (REF)"})
in
    Source

Capture.PNGCapture1.PNG

 

 

Super User
Super User

Re: Power Query - Change Type from a range os columns by position

The function is also very simple, as it takes just one parameter (a table) as it's input. So you would adjust your query to this:

 

let
    Source = Table.Combine({#"fUsage-Peak (REF)", #"fUsage_Off-Peak (REF)", #"fCIP (REF)", #"fTotal Cost (REF)", #"fExcedente Energia Reativa P (REF)", #"fExcedente Energia Reativa FP (REF)", #"fDemand Contracted Off-Peak (VERDE) (REF)", #"fDemand Registered Peak (Actual Null) (REF)", #"fDemand Contracted Peak (VERDE) (REF)", #"fDemand Registered Off Peak (Actual Null) (REF)", #"fDemand Registered Peak (Dem Peak) (REF)", #"fDemand Registered Off Peak (Dem Peak) (REF)", #"fDemanda Contracted Peak (AZUL) (REF)", #"fDemanda Contracted Off Peak (AZUL) (REF)", #"fReactive Demand Peak (REF)", #"fReactive Demand Off Peak (REF)"}),
    ApplyFunction = FunctionName(Source)
in ApplyFunction

Where "FunctionName" is the name of the query that holds the function code that you have copied.

 

Please see attached file.

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

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




View solution in original post

heitorleaof Frequent Visitor
Frequent Visitor

Re: Power Query - Change Type from a range os columns by position

It worked!

 

Thank you so much for the help!

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 46 members 989 guests
Please welcome our newest community members: