Forum Discussion
Create Table from list (Column name in list)
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"
Thanks for your help on this already! I should have mentioned though that I had a few steps configured prior to the example I provided. Are you able to help me adjust my M code with the example you have provided whilst including the steps already implemented below?
let
Source = Json.Document(Web.Contents("https://euw1.brightpearlconnect.com/public-api/XXX/order-service/sales-order-search?columns=customerId,salesOrderId,orderStatusId,taxDate", [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"}})
in
#"Renamed Columns"
thanks again!
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"