Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
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"
Hi,
It is not being able to recognise the correct table on the web page.
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"
Best Regards
Maggie
None that i can think of.
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"
User | Count |
---|---|
93 | |
84 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |