Create a calculate column with LEFT function using power Query IN a liveconnection

[Anonymous]

Hello everyone 

In my database i wanted to join By just the left part of the CONTRACT  column 

I use a directQuery with my database so i couldn't split the column by delimiter , so i tried to Create a calculate column Using LEFT Function but it doesn't work .

INDIVIDU = LEFT(PCONT[CONTRAT];22)

PS: i created the calculate column in POWER BI DESKTOP  it works but it doesn't appear when i Open the Direct Query .

[MFelix]

Hi @Anonymous,

The Power BI uses two different languages:

• DAX for the front end
• M Language for the Query editor

DAX uses a language similar to Excel so you use LEFT but in M language the formulas are different. For making this in Query editor you need to use the Text.Start function see the details here.

Regarding the second part you refer (column made in PBI not appearing in Query editor) be aware that all you do in the Query editor reflects on the "front end" of PBI however only some things you do on the front end reflects on the Query editor, examples renaming columns, deleting them goes back to the query editor. Creating a new column with DAX doesn't reflect on the query editor.

Regards,

MFelix

[Anonymous]

thank You very Much that was so helpful .

 i have  another Question , i mentioned  that i'm using a directQuery is it possible somehow to split a column ?! because i tried to use the function Text.Start  it works but i have an error " This step results in a query that is not supported in DirectQuery mode."

[MFelix]

Hi @Anonymous,

Using the direct query adds limitations to the data handling in Query Editor.

Check this documentation with the full details.

You need to make a DAX column, in practice this does not change anything in the way you handle it to do measures or visuals, the only impact is that this column will no appear in the query editor.

Regards,

MFelix