cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Regular Visitor

How to edit the table in the query

I need your advice how to edit the query.

I received the table as excel file as below.

 

Date (Day/Month/Year)  A (order)  B(order)  C(order)

1/1/2021    88    570    183

1/2/2021    86    550    170

1/3/2021    80    550     100

 

This table shows the "number of the products (A, B and C) for daily order".

Hence the number of products, which are written in the column at 1/1/2021, equals the number of products will be ordered everyday from 1st January 2021 to 31st January 2021.

 

I would like to edit the table by Power BI query as below.

The numbers of daily order will be written in each day.

Would you mind telling the method how to edit ?

I don't want to edit the excel file and hope to complete the edition of the table in query.

 

Date (Day/Month/Year)  A (order)  B(order)  C(order)

1/1/2021    88    570    183

2/1/2021    88    570     183

 

31/1/2021  88   570   183

1/2/2021    86    550    170

2/2/2021    86    550     170

 

28/2/2021   86   550   170

1/3/2021    80    550     100

2/3/2021    80    550     100

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Helper III
Helper III

Re: How to edit the table in the query

PFB the attachment for reference. 

 

Accept solution if it resolves your requirement.

daily order products.pbix 

View solution in original post

Highlighted
Community Support
Community Support

Re: How to edit the table in the query

hi  @KYOSHIKAWA 

For your case, you could use solution as labuser1235 provided, and you'd better add a year column and in the two tables and add this conditional in LOOKUPVALUE Function.

For example:

A Column = LOOKUPVALUE(Sheet1[A],Sheet1[Month],'Date'[Month],Sheet1[Year],'Date'[Year])
 
and you could also try this way, use M code create a dim date table as below:
Then use Merge Function in edit queries to merge two tables by Year and Month column, then expand it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
Highlighted
Helper III
Helper III

Re: How to edit the table in the query

PFB the attachment for reference. 

 

Accept solution if it resolves your requirement.

daily order products.pbix 

View solution in original post

Highlighted
Community Support
Community Support

Re: How to edit the table in the query

hi  @KYOSHIKAWA 

For your case, you could use solution as labuser1235 provided, and you'd better add a year column and in the two tables and add this conditional in LOOKUPVALUE Function.

For example:

A Column = LOOKUPVALUE(Sheet1[A],Sheet1[Month],'Date'[Month],Sheet1[Year],'Date'[Year])
 
and you could also try this way, use M code create a dim date table as below:
Then use Merge Function in edit queries to merge two tables by Year and Month column, then expand it.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Regular Visitor

Re: How to edit the table in the query

Thank you for your advice.

Your editted query is useful.

However "lookupvalue" function was not recognized in my Power BI...

Anyway thank you for your support.

 

Best regards,

Yoshi

Regular Visitor

Re: How to edit the table in the query

Thank you for your advice.

In my Power BI, lookupvalue function was not recognized...

I will check it again.

And I will check the site which you told the link.

 

Best regards,

Yoshi

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors