Creating a new table from an URL Web page

[andresgl06]

Hi everyone,

I´m having the following issue, i connected my power bi to an online page that posts a financial value everyday.

Here´s a look at the table:

You can see that the first column corresponds to the Day of the Month, and then the other columns are the months. For example; second column equals to January, Third column equals to February and so on. In order for this table to be useful to me, i need to create a new table with two columns, one with the date and another one with the value for that date. I tried transposing but didn´t worked as i expected. Any ideas on how i can obtain that ?

[Ashish_Mathur]

Hi,

In the Query Editor, right click on the heading of he first column and select "Unpivot other columns".  Then in a calculated column formula, you may write this DAX formula to create a Date

Date = 1*(Data[Dia]&"/"&Data[Month]&"/2021")

Hope this helps.

[FrankAT · Accepted solution]

Hi @andresgl06 ,

you can do it in Power Query (see attached PBIX file):

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RdLJkQQhDARAX/pdD3SCbOkY/91YBhWzPwUBqYv3feTBozWyYHVC0UTECbUcuU5olch5Qq+FkSdMmRjz+eB9lM6CJ51N9gsbgrrOQvSF+D9NHVjrOEZnIpoUUwgfj4J26GPAg06ghI6jy3Eyu5dFpm+b/I58KOwaheU0doVykCASSCcysTqVqWEVHYf2hRDD5Gx23WbHSTrf4g7jA9mNmgnkMgm5zEQWmX0ah5lkfinEDe50dtVk9nrIqCCVTKHyMIuMYhmZhLZoHrdB3yMpoxP3S+ROOXtTRUfuHMQLg5sKg3RmF7mJYn8J58Z9d/h8Pn8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dia = _t, Ene = _t, Feb = _t, Mar = _t, Abr = _t, May = _t, Jun = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Dia", Int64.Type}, {"Ene", type number}, {"Feb", type number}, {"Mar", type number}, {"Abr", type number}, {"May", type number}, {"Jun", type number}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Dia"}, "Attribute", "Value"),
    #"Merged Queries" = Table.NestedJoin(#"Unpivoted Other Columns", {"Attribute"}, #"Month table", {"Monthname"}, "Month table", JoinKind.LeftOuter),
    #"Expanded Month table" = Table.ExpandTableColumn(#"Merged Queries", "Month table", {"Monthnumber"}, {"Monthnumber"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Month table", "Date", each #date(Date.Year(DateTime.LocalNow()),[Monthnumber],[Dia])),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom",{"Date", "Value", "Dia", "Attribute", "Monthnumber"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Reordered Columns",{"Date", "Value"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Date", type date}})
in
    #"Changed Type1"

// Month table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs1LVdJRMlSK1YlWcktNArKNwGzfxCIg2xjMdkwCsU2g4pVAtimY7VWaB2SbKcXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Monthname = _t, Monthnumber = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Monthname", type text}, {"Monthnumber", Int64.Type}})
in
    #"Changed Type"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[andresgl06]

This did exactly what i needed, thanks a lot man !