Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Robert Jolliffe]

Hi Everyone,

Sorry if this has been asked and answered, I may be searching on the wrong keywords.

I have 3 tables that look like this:

TABLE DATA
ID   NAME
1     JO
2     SUE

TABLE CUSTOM_FIELD_DATA
DATA_ID   FIELD_ID   DATA
1                1                SMITH
1                2                33
2                1                JONES
2                2                22

TABLE CUSTOM_FIELDS
ID        NAME
1          LAST_NAME
2          AGE

I want to create a query that looks like this
QUERY DATA
ID     NAME    LAST_NAME    AGE
1       JO          SMITH              33
2       SUE       JONES              22

Any ideas?  I am fairly new to Power BI – old hand at SQL,  In SQL I’d probably make the LAST_NAME and AGE fields sub-queries.  There are about 3-5 of these fields out of 20 or so.

Thanks very much

——————————
Robert Jolliffe
President, Sabre Limited
Cambridge
5195857524
——————————

[Vishesh Jain]

Hi ,

Please have a look at the attached file.

I have merged all 3 queries into one, removed the unnecessary columns and pivoted a column, which you can see step by step in the query transformation.?

The query is named Merge1.

The same can be achieved by using the RELATED() DAX function in the table view, but from a performance perspective its better that its done in Power Query.

Hope this solves your problem.

Thank you,

——————————
Vishesh Jain
Owner
VR Construction
——————————
——————————————-