Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Anonymous
Not applicable

Convert table data from one to another

‎04-18-2021 11:36 PM

How can i convert below data?

 

Base Data

Column1Column2Column3
Friday April 16 2021Friday April 16 2021Friday April 16 2021
Friday April 16 2021Friday April 16 2021Friday April 16 2021
11:30 AMINForeign Exchange Reserves
Friday April 23 2021Friday April 23 2021Friday April 23 2021
Friday April 23 2021Friday April 23 2021Friday April 23 2021
11:30 AMINDeposit Growth YoY
11:30 AMINForeign Exchange Reserves
11:30 AMINBank Loan Growth YoY
Friday April 30 2021Friday April 30 2021Friday April 30 2021
Friday April 30 2021Friday April 30 2021Friday April 30 2021
11:30 AMINInfrastructure Output YoY
11:30 AMINForeign Exchange Reserves 
Monday May 03 2021Monday May 03 2021Monday May 03 2021
Monday May 03 2021Monday May 03 2021Monday May 03 2021
05:00 AMINMarkit Manufacturing PMI 
12:20 PMINBalance of Trade Prel 
12:20 PMINExports Prel 
12:20 PMINImports Prel
Wednesday May 05 2021Wednesday May 05 2021Wednesday May 05 2021
Wednesday May 05 2021Wednesday May 05 2021Wednesday May 05 2021
05:00 AMINMarkit Composite PMI 
05:00 AMINMarkit Services PMI
Friday May 07 2021Friday May 07 2021Friday May 07 2021
Friday May 07 2021Friday May 07 2021Friday May 07 2021
11:30 AMINDeposit Growth YoY
11:30 AMINForeign Exchange Reserves

11:30 AMINBank Loan Growth YoY

 

 

Data After Conversion

DateEvent
16-Apr-21Foreign Exchange Reserves
23-Apr-21Deposit Growth YoY
23-Apr-21Foreign Exchange Reserves
23-Apr-21Bank Loan Growth YoY
30-Apr-21Infrastructure Output YoY
30-Apr-21Foreign Exchange Reserves 
03-May-21Markit Manufacturing PMI 
03-May-21Balance of Trade Prel 
03-May-21Exports Prel 
03-May-21Imports Prel
05-May-21Markit Composite PMI 
05-May-21Markit Services PMI
07-May-21Deposit Growth YoY
07-May-21Foreign Exchange Reserves

07-May-21Bank Loan Growth YoY

 

Since i dont use previous cell value selection like Excel..Kindly help

Your help will be appriciated

Thanks in Advance

Message 1 of 5
512 Views
0
2 ACCEPTED SOLUTIONS
ImkeF
Super User
Super User

‎04-19-2021 05:22 AM

Hi @Anonymous  

the following steps should get you there:

  1. Duplicate Column2
  2. Check that new column and replace "IN" by null
  3. Check the new column -> rightclick mouse -> Fill -> Down
  4. Filter Column2 on "IN"
  5. Remove Column1 and Column2

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

View solution in original post

Message 3 of 5
426 Views
0
v-angzheng-msft
Community Support
Community Support

‎04-20-2021 09:29 PM

Hi, @Anonymous 

Refer to the method provided by @ImkeF 

v-angzheng-msft_0-1618979256169.jpeg

  1. Duplicate Column2
  2. Check that new column and replace "IN" by null
  3. Check the new column -> right click mouse -> Fill -> Down
  4. Filter Column2 on "IN"
  5. Remove Column1 and Column2

Note that the copied Date field(column 1 and column 2) is a text type that needs to be converted to a date before filled down.

To get the desired result, a calculate column is needed to format the date:

Date = FORMAT('Table'[Column2 - Copy], "dd-mmm-yy")

Result:

v-angzheng-msft_1-1618979256172.jpeg

v-angzheng-msft_2-1618979256175.jpeg

 

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

 

 

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

 

How to get your questions answered quickly -- How to provide sample data

View solution in original post

Message 4 of 5
387 Views
0
4 REPLIES 4
v-angzheng-msft
Community Support
Community Support

‎04-20-2021 09:29 PM

Hi, @Anonymous 

Refer to the method provided by @ImkeF 

v-angzheng-msft_0-1618979256169.jpeg

  1. Duplicate Column2
  2. Check that new column and replace "IN" by null
  3. Check the new column -> right click mouse -> Fill -> Down
  4. Filter Column2 on "IN"
  5. Remove Column1 and Column2

Note that the copied Date field(column 1 and column 2) is a text type that needs to be converted to a date before filled down.

To get the desired result, a calculate column is needed to format the date:

Date = FORMAT('Table'[Column2 - Copy], "dd-mmm-yy")

Result:

v-angzheng-msft_1-1618979256172.jpeg

v-angzheng-msft_2-1618979256175.jpeg

 

Is this the result you want? Hope this is useful to you

Please feel free to let me know If you have further questions

 

 

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

 

How to get your questions answered quickly -- How to provide sample data
Message 4 of 5
388 Views
0
Anonymous
Not applicable
In response to v-angzheng-msft

‎04-21-2021 08:58 PM

Thanks a lot @ImkeF @v-angzheng-msft @amitchandak for your kind support

Your effort has been appreciated!! Great

Message 5 of 5
364 Views
0
ImkeF
Super User
Super User

‎04-19-2021 05:22 AM

Hi @Anonymous  

the following steps should get you there:

  1. Duplicate Column2
  2. Check that new column and replace "IN" by null
  3. Check the new column -> rightclick mouse -> Fill -> Down
  4. Filter Column2 on "IN"
  5. Remove Column1 and Column2

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Message 3 of 5
427 Views
0
amitchandak
Super User
Super User

‎04-19-2021 12:37 AM

@Anonymous , need to check with M experts

 

@ImkeF , can you help on this



Message 2 of 5
462 Views
0

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

View All