Specify column type when choosing columns instead of afterwards

[Anonymous]

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}})

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

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.

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

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.

[Anonymous]

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}})

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

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

[Anonymous]

@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!!!