Forum Discussion
Creating a new table from an URL Web page
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 ?
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)
3 Replies
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)This did exactly what i needed, thanks a lot man !
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.
