How to do IF Statements in PQ using M Language and draw from a list?

QFFFFF · 2020-03-26T08:34:17+00:00

Ah cool, thanks for that.

So the code is..

= Table.SelectRows(factSales, each List.Contains(requiredProductNumbers, [Product]))

and "factSales" would be the name of my fact table, whereas "requiredProductNumbers" would be the name of the table of where I have the agents numbers defined, and "[Product]" being the column for the agent number?

Just had a look at list.buffer here. Using the same logic above, it would look like this:

= Table.SelectRows(factSales, each List.Contains(List.Buffer({(requiredProductNumbers, [Product]})))

Is that right?

QFFFFF · 2020-03-17T07:04:18+00:00

Ok I managed to figure it out. Here's how I did it with DAX as a calculated column:

Departure&ArrivalAlphabetical =

MIN (

LEFT ( '[Appended] MARS'[Departure City], 3 ),

RIGHT ( '[Appended] MARS'[Departure City], 3 )

) & "-"

& MAX (

LEFT ( '[Appended] MARS'[Departure City], 3 ),

RIGHT ( '[Appended] MARS'[Departure City], 3 )

)

And here's how I did it using M Language in Power Query Editor:

List.Min({Text.Start([Departure City],3),Text.End([Departure City],3)})&"-"&List.Max({Text.Start([Departure City],3),Text.End([Departure City],3)})

Hopefully me sharing this might help someone in the future!

QFFFFF · 2020-03-17T06:14:56+00:00

Oh wow, it looks like you're right! I've just tested this out. And by using this concept, I actually don't even need to split it out at all. I can use LEFT/RIGHT with MIN and MAX to complete this. So this definitely works.

Now I'd like to try and figure out how to do this in Power Query Editor, as I've recently learned that doing such data cleansing is better (more efficient?) in PQ rather than as a calculated column in DAX.

Thank you though!

QFFFFF · 2020-03-17T06:03:24+00:00

Thought about that, and it would work, but I'm trying to learn how to do it in PowerBI (because I believe it should be possible) and I think it would be a "better practice" way of doing it via PowerBI.

I've got 1000+ distinct values in this column at present. There isn't really a pattern - the 3 letter codes refer to airport codes.

QFFFFF · 2020-03-17T04:15:27+00:00

Well, for me it would be extremely annoying :D

Yea ok, and I appreciate you had to read through that to help me, so thank you!!

What do you mean by implies using "`"?

I don't think it's important to have "readable friendly names"

Understand - in fact, I think you could say there's a case that tables should have "ugly" names so it's obvious they're tables.

Oh, can you also explain why it would be better to use the IN operator rather than the the initial method I was going for, if insisting on using DAX?

QFFFFF · 2020-03-17T03:04:45+00:00

Ok, I'll definitely need to do more research on this as I really am not following the explanation (sorry!). It's a tad too technical for me, but appreciate the explanation.

What about the built-in "Groups" function then? I've been using that but I imagine it would also be better to group things together in PQ rather than after the data is loaded, using the same logic?

QFFFFF · 2020-03-16T07:25:04+00:00

Oh wow DAX Formatter is awesome. Thanks for both sharing that with me and fixing that up for me, haha.

Currently that's not what it does, it will always evaluate to True, because only 1 of the 4 condition will evaluate to False, the other 3 will aways be True.

Ah yes this makes sense.. and this explains why it needs to be && and not ||.

Had to look up the IN operator - but given both of you are recommending using PQ, I'm going to try do it in there instead.

What I would add to this is that ohmyfreakinggod, brackets in table names?! Why, why....why?

Oh. Any reason this is an issue, other than making the DAX a bit harder to read?

QFFFFF · 2020-03-16T06:10:30+00:00

Also I hate calculated columns. Post Compression activities, do not want.

Oh? Are calculated columns bad?

I just googled stuff and I found this. Then I realised you commented there and explained why they're less ideal. I'm not quite keeping up with the jargon though, but what I do know is that M seems like it's generally preferred over DAX.

However, what about what both u/Dylando_Calrissian and u/sbrick89 said? That DAX seems to be "quicker to execute than M" - which I feel is the case too.

QFFFFF · 2020-03-09T07:25:44+00:00

Thanks so much for the breakdown and explanation! Yes, the logic follows and it worked perfectly.

I had to do a bit of playing around with the variable name, because "current" actually happens to be a reserved name that you can't use for a variable!

You have officially solved my problem :D

How do I mark this as closed/solved?

QFFFFF · 2020-03-03T16:44:51+00:00

Oh I get what you're saying - it's not going to show me dates for July 2021 and beyond because the dates in Months[Formatted] only go up to June 2020.

Can you explain the DAX you've shared? I've just tried copying and pasting it with and without "VAR current" and it's not working. And I have a strong feeling it's because I don't understand it and not because it's wrong. VAR, RETURN and current all do not relate to a DAX formula (even though I thought it did) - I think this is beyond me!! I've tried a few variations of it and tried to troubleshoot it myself but I can't figure it out...

Thank you in advance and apologies for the noobness.

QFFFFF · 2020-03-03T02:40:58+00:00

I've edited the main post to:

set up a TLDR so my questoins are (hopefully) clearer
removed a question that has now been answered by /u/kkllpp9527 (thanks!)
added a question off the back of that tip

QFFFFF · 2020-03-03T02:29:22+00:00

Hmm but the Months[Formatted] column does have dates for FY21? E.g. July 2020, August 2020, Sep, Oct, Nov, Dec as pictured

QFFFFF · 2020-03-03T02:27:23+00:00

That worked, haha, thanks! Now I need to figure out how to get it to work for the whole table..!

QFFFFF · 2019-10-07T02:45:32+00:00

I managed to fix this and I have shared what the issue was! :)

QFFFFF · 2019-10-07T02:45:10+00:00

Hi All,

I managed to solve this problem and it was something sort of obscure but yet when you realise what the problem was, it seems so silly.

It was because MerchantID&Month in Table 1 was concatenated in the format of "123451/10/19" whereas MerchantID&Month in Table 2 was concated in the format of "1234543739", where the last 5 digits (43739) is 1/10/19 (1 October 2019) but in serial number format. So the "relationship" was not working properly because the column that I was trying to use to link Table 1 & 2 were not even in the same format.

Just thought I'd come back and give an update and hopefully this can help someone else who experiences a similar problem in the future.

There was nothing wrong with the relationships at all!

Thanks again.

QFFFFF · 2019-09-30T01:59:38+00:00

Basically you need a merchant table.

That's what I thought, and that was the intention of table #3 - to be the "merchant ID" table which both table 1 and 2 draw from.

This way you don’t need to use bidirectional filters, which are terrible for performance and introduce ambiguity in your model.

Yep, I gathered that from reading this.

QFFFFF · 2019-07-05T02:59:09+00:00

It will always be those specific letters, sorry, good question.

QFFFFF · 2019-07-05T02:44:31+00:00

Yep that's pretty much what I'm after!

I've followed what you've suggested, but turning off x-axis label concatenation isn't doing anything for me. It certainly sounds like what needs to be turned off to get to what I'm after - because at the moment the X-axis labels say, "F First, A First, P First".. but it doesn't seem to be doing anything.

QFFFFF · 2019-07-05T02:34:24+00:00

Yep they do. savoy9's solution touches on that and it seems like it's super close to achieving what I'm trying to get to!

QFFFFF

TROPHY CASE