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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
JoaoMS
Helper III
Helper III

New table based on other tables SUMMARIZE? SUMMARIZECOLUMNS?

Hi all, I was trying to figure out how to create a new table (not in the visualization report) based on other 3 linked tables using SUMMARIZE or SUMMARIZECOLUMNS but I'm honest but got confused.

These three tables are:

1. Table "TotalWO": with a big list of registers related to Maintenance Work Orders (WO) released in our company during the past years, with their Status of execution and the Department in charge (mechanic, electrical or I&C),

2. Table "WO_2022": with the Work Orders released during this year and the week in which those WOs were executed, and

3. Table "Calendar": with columns associated with weeks and months.

In this figure I summarize these databases:

Figure 1Figure 1

The relation between them is a kind of obvious:

 

Figure 2Figure 2

And the expected output is as follows and in the same order of the columns, I mean that the first two columns should be the month and the week (from table "Calendar"), then the column "Department" (from table "TotalWO"), and so on.

 

Figure 3Figure 3

I know that in the visualization report I can use the "Table" visualization to get this however I need this new table (called for instance "Final Table"), in order to use it later in a Power Automate flow.

 

Hope I was clear and many thanks in advance.

 

Joao

1 ACCEPTED SOLUTION
parry2k
Super User
Super User

@JoaoMS create new table using following DAX expression:

 

New Table = 
SUMMARIZE (
    WO_2022, 
    'Calendar'[Month],
    'Calendar'[Weel],
    Total_WO[Department],
    WO_2022[WO],
    Total_WO[Status]
)

 

You can change the order of the columns the way you want.

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

View solution in original post

6 REPLIES 6
parry2k
Super User
Super User

@JoaoMS create new table using following DAX expression:

 

New Table = 
SUMMARIZE (
    WO_2022, 
    'Calendar'[Month],
    'Calendar'[Weel],
    Total_WO[Department],
    WO_2022[WO],
    Total_WO[Status]
)

 

You can change the order of the columns the way you want.

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank you! it worked as expected

parry2k
Super User
Super User

@JoaoMS so you only new table with wo_2022 workorders, correcT?



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Correct, it's just like the "WO_2022" table is being added with new columns but in a new table. Actually the the three tables have other columns that I'm not interested and that is why we need a new table relating just these columns, and in the order commented. thanks

parry2k
Super User
Super User

@JoaoMS can you confirm the following relationship:

 

calendar -> wo_2022 = 1 to many, 1 on the calendar side and many on wo_2022 side

Total_wo -> wo_2022 = 1 to 1 

 

is this correct understanding?

 

Follow us on LinkedIn and YouTube.gif to our YouTube channel

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k , you are right:

1. calendar -> wo_2022 = 1 to many. "Calendar" table has just 52 rows (52 weeks), but in the WO_2022 table we can find many "Work Orders" executed within the same week.

2. Total_wo -> wo_2022 = 1 to 1 . Correct, there is no duplicate.

Thanks for the quick reply

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors