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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
andresgl06
Frequent Visitor

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:

andresgl06_0-1621449540917.png

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 ?

1 ACCEPTED SOLUTION
FrankAT
Community Champion
Community Champion

Hi @andresgl06 ,

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

 

19-05-_2021_23-41-05.png

 

// 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)

View solution in original post

3 REPLIES 3
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
FrankAT
Community Champion
Community Champion

Hi @andresgl06 ,

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

 

19-05-_2021_23-41-05.png

 

// 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 !

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.