Forum Discussion

WillBatesHydro's avatar
WillBatesHydro
Helper I
4 years ago
Solved

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...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    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"

     

WillBatesHydro's avatar
WillBatesHydro
Helper I
4 years ago

Thanks, Jenratten! I tried your example without my source and seemed to work okay but struggled to adjust it with my existing M code steps. below is what I have already, are you able to add your code to this?

 

 

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"}})
in
#"Renamed Columns"

 

 

jennratten's avatar
jennratten
Super User
4 years ago

Sure thing - pls try this:

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 Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Index],each if [Field Name] = "Company ID" then [Index] else null,Replacer.ReplaceValue,{"Index"}),
    #"Filled Down" = Table.FillDown(#"Replaced Value",{"Index"}),
    #"Pivoted Column" = Table.Pivot(#"Filled Down", List.Distinct(#"Filled Down"[#"Field Name"]), "Field Name", "Values"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"