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
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
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.