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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mafia7759
New Member

Help with transforming Data from Table

Hello,

 

I'm kinda new in power BI and i have some trouble with this.

 

I need to change some $ values to UF ( just divide them $ / UF ) . The  value UF i can have it from a CSV file from this web page: "http://www.sii.cl/valores_y_fechas/uf/uf2018.htm".

 

Uploaded Data CSV UF value per dayUploaded Data CSV UF value per day

 

And I want to transform this data to something like this.

 

What i want.png

 

 

So in the end i think i'm going to upload this data to call it and use the value of UF depending on the day and do the division of the value $ according to the value of UF of X day.

 

Thank you very much.

1 ACCEPTED SOLUTION

@Mafia7759

 

Hi, please follow these steps in Query Editor

 

1. Select all the months columns

 

2. Unpivot Columns

 

3. Add a conditional column to assign a Month Number to each Month..Ex: Ene --> 1 Feb --> 2 and so on

 

4.Add a custom column

 

Date.FromText("2018-" & Text.From([Custom]) & "-" & Text.From([Día]))

Custom is the column created in step 2

 

Ready

 

Regards

 

Victor




Lima - Peru

View solution in original post

4 REPLIES 4
v-danhe-msft
Employee
Employee

Hi @Mafia7759,

Based on my test, you could refer to below steps in query editor:

Sample data:

1.PNG

Unpivot the [December] function:

1.PNG

Add a customer column and use the Text.Combine function:

Text.Combine({"2018",
Text.Combine({[Attribute],[Day]}, "-")},"-")

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

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

Hi @v-danhe-msft , Thank you for your reply. 

 

I tried your solution and did work. But when i try to transform the created column to a date it shows error. The other thing i would like to solve is that when you use more than 1 month, i should try to separate so that i have all the months in just 1 column like the example I gave in the beginning ( so later i can call it correctly using another data set so that i can use the correct division depending on the date) . If I apply your solution to two months it show like this.

 

Created ColumnCreated Column

 

 

Thank you in advance.

@Mafia7759

 

Hi, please follow these steps in Query Editor

 

1. Select all the months columns

 

2. Unpivot Columns

 

3. Add a conditional column to assign a Month Number to each Month..Ex: Ene --> 1 Feb --> 2 and so on

 

4.Add a custom column

 

Date.FromText("2018-" & Text.From([Custom]) & "-" & Text.From([Día]))

Custom is the column created in step 2

 

Ready

 

Regards

 

Victor




Lima - Peru

@Vvelarde that worked great ! . Thank you very much.

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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