how to seperate columns and align the columns

[tauqeer]

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.

[tauqeer]

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"

[Ashish_Mathur]

Hi,

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

[tauqeer]

Hi Ashish

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

[v-juanli-msft]

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

[tauqeer]

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

[v-juanli-msft]

Hi @tauqeer

Does my answer slove your problem finally?

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

Best Regards

Maggie

[v-juanli-msft]

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"

Best Regards

Maggie

[Ashish_Mathur]

None that i can think of.

[Ashish_Mathur]

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"