Forum Discussion
WillBatesHydro
4 years agoHelper I
Create Table from list (Column name in list)
This should be super easy to achieve but can not for the life of me work out how (long day) As per the image above, I have two columns, one containing the headers (Column names) and the...
- 4 years ago
Use this. Check your source again as .... has come in because of copy and paste.
let Source = Json.Document(Web.Contents("https://euw1.brightpearlconnect.com/public-api/XXX/order-service/sales-order-search?columns=customer...", [Headers=[#"brightpearl-app-ref"="XXXX", #"brightpearl-account-token"="XXX"]])), response = Source[response], results = response[results], #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandListColumn(#"Converted to Table", "Column1"), #"Added Index" = Table.AddIndexColumn(#"Expanded Column1", "Index", 0, 1, Int64.Type), #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],4) + 1), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Custom.1", each if [Custom] = 1 then "Company ID" else if [Custom] = 2 then "Order ID" else if [Custom] = 3 then "Order Type" else if [Custom] = 4 then "Tax Date" else null), #"Removed Columns" = Table.RemoveColumns(#"Added Conditional Column",{"Index", "Custom"}), #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Custom.1", "Column1"}), #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Custom.1", "Field Name"}, {"Column1", "Value"}}), #"Added Index1" = Table.AddIndexColumn(#"Reordered Columns", "Index1", 0, 1, Int64.Type), #"Divided Column" = Table.TransformColumns(#"Added Index1", {{"Index1", each _ / 4, type number}}), #"Rounded Down" = Table.TransformColumns(#"Divided Column",{{"Index1", Number.RoundDown, Int64.Type}}), #"Pivoted Column" = Table.Pivot(#"Rounded Down", List.Distinct(#"Rounded Down"[#"Field Name"]), "Field Name", "Value"), #"Removed Columns1" = Table.RemoveColumns(#"Pivoted Column",{"Index1"}) in #"Removed Columns1"
BA_Pete
4 years agoSuper User
Hi WillBatesHydro ,
Select [Field Name] column.
Go to Transform tab > Pivot Column > Advanced Options = Don't Aggregate
Pete
WillBatesHydro
4 years agoHelper I
Thank you so much Pete! Almost there, it's just thrown out the below error;
Any Ideas?
- BA_Pete4 years agoSuper User
Yup, sorry, my mistake.
Forgot about enumeration elements issue.
My bad. 😞
Pete