Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
aakhibsyed
Regular Visitor

How to use RELATED DAX in power query to add columns

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
6 REPLIES 6
edhans
Super User
Super User

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
AlB
Super User
Super User

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 

 

SU18_powerbi_badge

 

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

 

aakhibsyed_0-1604580597409.png

 

CNENFRNL
Community Champion
Community Champion

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


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

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

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"

 

 


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors