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: 

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
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
470 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
384 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
345 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
346 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
322 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
385 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
420 Views
0

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.

View All