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
tauqeer
Regular Visitor

how to seperate columns and align the columns

Hi

 

I dowmloaded this datafrom web,  please advise how do i align these multiple columns in to one.

As col 1 -4   has to be apend with col 5-8 as col 5-8 are the additional data, how do i align them , please advise.

 

I am new to power BI.

data.JPG

 

 

 

9 REPLIES 9
tauqeer
Regular Visitor

Hi Ashish, thanks for your message , thats exactly what i want, but i dont know where to insert this code, i tired to run this 

code in the advanced query editor , it came with this error.

I dont know where to write this code, in the query editor, advance editor in the view tab or somewhere else.

 

i changed the source as well.

 

my code was like this:

 

let
   Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/Demography_of_Australia")), Data1 = Source{1}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rank", Int64.Type}, {"Name", type text}, {"State", type text}, {"Pop.", Int64.Type}, {"Rank2", Int64.Type}, {"Name2", type text}, {"State2", type text}, {"Pop.2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Rank", "Name", "State", "Pop.", "Rank2", "Name2", "State2", "Pop.2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index"}, "Attribute", "Value"),
    #"Inserted First Characters" = Table.AddColumn(#"Unpivoted Other Columns", "First Characters", each Text.Start([Attribute], 4), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted First Characters",{"Attribute"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Index", "First Characters", "Value"}),
    Partition = Table.Group(#"Reordered Columns1", {"First Characters"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Value", "Index1"}, {"Index", "Value", "Index1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Partition",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"First Characters"]), "First Characters", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns2",{{"Rank", Order.Ascending}})
in
    #"Sorted Rows"

 

error.JPG

Hi,

 

It is not being able to recognise the correct table on the web page.


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

Hi Ashish

 

so what will be the best way to do that, any solution.

Hi @tauqeer

I test with the url you provide and select the table as yours to import into power bi, finally i can get the result table successfully.

Please see my attachment for detailed information.

 

Best Regards

Maggie

Hi Maggie

I couldnt open this attachment, could you please advise in the comments.  or any other form of attachement as pdf etc.

 

thanks

 

 

Tauqeer

Hi @tauqeer

Does my answer slove your problem finally?

If so, could you kindly acept this answer as a solved solution?

 

Best Regards

Maggie

Hi @tauqeer

Below is my code in advanced editor, before writing this code i only Get data->web->paste the url and select anonymous, then go to edit queries.

let
    Source = Web.Page(Web.Contents("https://en.wikipedia.org/wiki/Demography_of_Australia")),
    Data1 = Source{1}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data1,{{"Header", type text}, {"", type text}, {"Rank", Int64.Type}, {"Name", type text}, {"State", type text}, {"Pop.", type number}, {"Rank2", Int64.Type}, {"Name2", type text}, {"State2", type text}, {"Pop.2", type number}, {"2", type text}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Rank", "Name", "State", "Pop.", "Rank2", "Name2", "State2", "Pop.2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index"}, "Attribute", "Value"),
    #"Inserted First Characters" = Table.AddColumn(#"Unpivoted Other Columns", "First Characters", each Text.Start([Attribute], 4), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted First Characters",{"Attribute"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Index", "First Characters", "Value"}),
    Partition = Table.Group(#"Reordered Columns1", {"First Characters"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Value", "Index1"}, {"Index", "Value", "Index1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Partition",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"First Characters"]), "First Characters", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns2",{{"Rank", Order.Ascending}})
in
#"Sorted Rows"

 

7.png

Best Regards

Maggie

None that i can think of.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

 

With the data on the left, the following M code returned the data on the right.

 

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Rank", Int64.Type}, {"Name", type text}, {"State", type text}, {"Pop.", Int64.Type}, {"Rank2", Int64.Type}, {"Name2", type text}, {"State2", type text}, {"Pop.2", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Index",{"Index", "Rank", "Name", "State", "Pop.", "Rank2", "Name2", "State2", "Pop.2"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", {"Index"}, "Attribute", "Value"),
    #"Inserted First Characters" = Table.AddColumn(#"Unpivoted Other Columns", "First Characters", each Text.Start([Attribute], 4), type text),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted First Characters",{"Attribute"}),
    #"Reordered Columns1" = Table.ReorderColumns(#"Removed Columns",{"Index", "First Characters", "Value"}),
    Partition = Table.Group(#"Reordered Columns1", {"First Characters"}, {{"Partition", each Table.AddIndexColumn(_, "Index1",1,1), type table}}),
    #"Expanded Partition" = Table.ExpandTableColumn(Partition, "Partition", {"Index", "Value", "Index1"}, {"Index", "Value", "Index1"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Partition",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[#"First Characters"]), "First Characters", "Value"),
    #"Removed Columns2" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}),
    #"Sorted Rows" = Table.Sort(#"Removed Columns2",{{"Rank", Order.Ascending}})
in
    #"Sorted Rows"

 

Untitled.png


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

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.