Help with transforming Data from Table

[Mafia7759]

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 day

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

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.

[v-danhe-msft]

Hi @Mafia7759,

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

Sample data:

Unpivot the [December] function:

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

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

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

Regards,

Daniel He

[Mafia7759]

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 Column

Thank you in advance.

[Vvelarde · 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

[Mafia7759]

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