Create Table from list (Column name in list)

[WillBatesHydro]

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 other including the corresponding value. 

As you can imagine all I want is a table with 4 columns  - Company ID, Order ID, Order Type and Tax date with the values for each below them 

so just like the table as displayed in excel below. I suspect I need to transpose something but can't figure it out

Thank you so much for your help in advance 

Will

[jennratten]

Hello - this will do it.  To apply this to your query, copy the steps below beginning with #"Added Index", add them to your query and change [Column1] in the #"Replaced Value" step to [Company ID].

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PKc3NM1TSUTJUitWB8Y2AfCMkvjGQb4zENwHyTZD4A6Q/FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Field Name" = _t, Values = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Field Name", type text}, {"Values", Int64.Type}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1, Int64.Type),
    #"Replaced Value" = Table.ReplaceValue(#"Added Index",each [Index],each if [Field Name] = "Column1" 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"

BEFORE

RESULT

[WillBatesHydro]

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]

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"

[BA_Pete]

Hi @WillBatesHydro ,

Select [Field Name] column.

Go to Transform tab > Pivot Column > Advanced Options = Don't Aggregate

Pete

[WillBatesHydro]

Thank you so much Pete! Almost there, it's just thrown out the below error; 

Any Ideas?

[BA_Pete]

Yup, sorry, my mistake.

Forgot about enumeration elements issue.

My bad. 😞

Pete

[Vijay_A_Verma]

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test (later on when you use the query on your dataset, you will have to change the source appropriately. If you have columns other than these, then delete Changed type step and do a Changed type for complete table from UI again)

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wcs7PLUjMq1TwdFHSUTI2MleK1YlW8i9KSS2CCBkiCYRUFqQChUzAQiGJFQouiSUgAUMjfUNDfSMDIyMFQyMrAwMgUnD0BatCNd7QEt14Y0zjDY3QzTfVNzIGGW+IanwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Field Name" = _t, Value = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each _ / 4, type number}}),
    #"Rounded Down" = Table.TransformColumns(#"Divided Column",{{"Index", Number.RoundDown, Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Rounded Down", List.Distinct(#"Rounded Down"[#"Field Name"]), "Field Name", "Value"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Company ID", Int64.Type}, {"Order ID", Int64.Type}, {"Order Type", Int64.Type}, {"Tax Date", type datetime}})
in
    #"Changed Type"

[WillBatesHydro]

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=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"

thanks again!

[Vijay_A_Verma · Accepted solution]

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"