How to use RELATED DAX in power query to add columns

[aakhibsyed]

Hello All experts,

I am new to power BI and have been working on this for a month now. 

I have updated the table using the RELATED query on DAX. 

Status = RELATED(STATUS_TYPE[STATUS_TYPE_NAME])

The above query will add a new column and updates the values with the relative values from the other table. 

I have this exercise to be done on 10 different tables.. Followed by each calculative measures. I cannot keep building the data and doing the same exercise again and again and also it is exhauting and not a smart way to work.. Also I get confused and mess up the queries. 

So I was thinking of recording my steps and store queries and run it to the every new table loaded through SQL import. 

My immediate understanding was power query.. which records the steps. But I need help.  Can anyone please help. 

Regards

Aakhib Syed

[edhans]

I suspect you want a merge @aakhibsyed  - can you provide some actual data using the info in the links below so we can assist? Conceptually, a Merge (Join) will work like RELATED/RELATEDTABLE in DAX, or VLOOKUP in Excel.  There is a good article here on how it works. The example uses Excel, but Power Query in Excel and Power BI are identical in this respect. 

To really assist though, I'd need sample data to demonstrate. I cannot copy and paste a screenshot into Power BI. Need a table. Again, the link below shows how to do that.

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[AlB]

Hi @aakhibsyed 

You can probably create a function in PQ, yes. Can you show samples of the tables and an example of the exact operation you need to perform on them?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[aakhibsyed]

Below is the sample data.

I have tables in the right side.. which I am pulling related data to the new column.. and the ones highlighted in Magenta are the calculative columns. 

closeinmins = DATEDIFF(Monthly[REPORT_DATE],Monthly[CLOSE_DATE],MINUTE)

Dueinmins = IF(Monthly[PRIORITY_TYPE_ID]=5,"240",IF(Monthly[PRIORITY_TYPE_ID]=6,"2880",IF(Monthly[PRIORITY_TYPE_ID]=7,"1440",IF(Monthly[PRIORITY_TYPE_ID]=8,"10080",IF(Monthly[PRIORITY_TYPE_ID]=9,"480",IF(Monthly[PRIORITY_TYPE_ID]=10,"20160"))))))

On_time = IF((Monthly[CloseInMins]<JOB_TICKET[DueInMins]&&Monthly[CloseInMins]>0),"On Time")

[CNENFRNL]

@aakhibsyed , DAX formulae vs M language (native language of Power Query) queries have nothing to do with each other, just like English and Chinese.

[aakhibsyed]

Thanks for clearing this..Can you please guuide towards the solution. 

Without getting into Power query (Chinese) 🙂 is there any way I can do it in DAX to run the procedures and steps to preapre my data in a quick. or I can record it somewhere to run it again and again. 

Some of the content is variable per each dataset. Can I use power query for this?? 

Kindly help

[CNENFRNL]

Hmm.. a bit difficult to break it down to very detals, I can only sketch it out like this,

Create a blank query in Power Query and name it TECH_GROUP

let
    STATUS_TYPE = 
        let
            Source = #table({"STATUS_TYPE_NAME", "STATUS"}, {{"A",1},{"B",2}})
            /*
                other steps
            */
        in
            Source,
    
    Source = #table({"STATUS_TYPE_NAME","Qty"}, {{"B",100},{"A",200},{"B",150}}),

    RELATED = Table.NestedJoin(Source, {"STATUS_TYPE_NAME"}, STATUS_TYPE, {"STATUS_TYPE_NAME"}, "STATUS_TYPE", JoinKind.LeftOuter),
    #"Expanded STATUS_TYPE" = Table.ExpandTableColumn(RELATED, "STATUS_TYPE", {"STATUS"}, {"STATUS"})
in
    #"Expanded STATUS_TYPE"