cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
heytherejem
Post Prodigy
Post Prodigy

Specify column type when choosing columns instead of afterwards

Hi there

 

I'm trying to make my M Query steps as efficient as possible. I a table I pull in from SharePoint, the first thing I do is specify the columns I want to keep, and then my next step is to change the column type because they're all ABC123.

 

I would love to be able to combine these steps. I tried, but I got an error.

 

This is the first and second steps, if there is a way I can specify the column type within the first code, please let me know 🙂

= Table.SelectColumns(PreviousStep,{"Received Date", "EditorId", "AuthorId"})

 

= Table.TransformColumnTypes(PreviousStep,{{"Received Date", type date}, {"AuthorId", Int64.Type}, {EditorId,Int64.Type}})

 

2 ACCEPTED SOLUTIONS
PhilipTreacy
Super User
Super User

Hi @heytherejem 

You can combine both steps like this

 

= Table.TransformColumnTypes(Table.SelectColumns(PreviousStep,{"Received Date", "EditorId", "AuthorId"}),{{"Received Date", type date}, {"AuthorId", Int64.Type}, {"EditorId",Int64.Type}})

 

regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Hi @heytherejem 

Start with the later step and embed the earlier step in it like this

= Table.TransformColumnTypes(Table.CombineColumns(PreviousStep,{"Month", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Audit Period"),{{"Audit Period", type date}})

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

4 REPLIES 4
PhilipTreacy
Super User
Super User

Hi @heytherejem 

You can combine both steps like this

 

= Table.TransformColumnTypes(Table.SelectColumns(PreviousStep,{"Received Date", "EditorId", "AuthorId"}),{{"Received Date", type date}, {"AuthorId", Int64.Type}, {"EditorId",Int64.Type}})

 

regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

Thanks @PhilipTreacy 

I've tried to apply the same logic to a different argument. I am combining two fields, and the result of the merge is a date.

So, my separate steps are:

= Table.CombineColumns(PreviousStep,{"Month", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Audit Period")
= Table.TransformColumnTypes(AuditPeriod,{{"Audit Period", type date}})

 

I tried applying your logic but it says 5 arguments were passed to a function that expects 4. This is what I tried:

= Table.CombineColumns(PreviousStep,{"Month", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Audit Period",{{"Audit Period", type date}})

 

Hi @heytherejem 

Start with the later step and embed the earlier step in it like this

= Table.TransformColumnTypes(Table.CombineColumns(PreviousStep,{"Month", "Year"},Combiner.CombineTextByDelimiter("/", QuoteStyle.None),"Audit Period"),{{"Audit Period", type date}})

Regards

Phil



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


View solution in original post

@PhilipTreacy you are super clever! Yes I see now I missed the instruction at the beginning. This has really helped me understand M Query better, thank you so much!!!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.