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

Create Table from list (Column name in list)

9 Replies

Super User

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

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

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"

Vijay_A_Verma

Most Valuable Professional

4 years ago

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
Helper I
4 years ago
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!
- Vijay_A_Verma
  Most Valuable Professional
  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
Super User
4 years ago
Hi WillBatesHydro ,

Select [Field Name] column.

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

Pete
- WillBatesHydro
  Helper I
  4 years ago
  Thank you so much Pete! Almost there, it's just thrown out the below error;
  
  Any Ideas?
  - BA_Pete
    Super User
    4 years ago
    
    Yup, sorry, my mistake.
    
    Forgot about enumeration elements issue.
    
    My bad. 😞
    
    Pete

Forum Discussion

Create Table from list (Column name in list)

9 Replies

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Create columns group

Creating custom columns keeps returning a structured Table column