Forum Discussion
Expand Columns and Rows
Hi,
I’ve imported a Smartsheet into Power BI via the API key and Sheet ID.
The data contains a Column List that includes id, title, and other fields, and a Row List that holds the Cells data. When I expand the Cells section, it shows id and value pairs.
However, when I try to expand the Row List, the rows grow excessively large. Additionally, when attempting to merge and pivot columns, the dataset becomes enormous, leading to a huge number of rows.
I'm struggling to expand and extract the table correctly. Currently, I’ve connected using the Web Connector through Get data, but is there another method to handle this more efficiently?
Thanks in Advance..
RKK Hi! Here's a complete Power Query (M) example that takes the raw Smartsheet JSON structure (with columns and rows), and transforms it into a flat table, where each row from Smartsheet becomes a single row in Power BI — with column names as headers.
let
// Step 1: Load source JSON from the Web API
Source = Json.Document(Web.Contents("https://api.smartsheet.com/2.0/sheets/YOUR_SHEET_ID", [
Headers = [Authorization = "Bearer YOUR_API_KEY"]
])),// Step 2: Create a lookup table of column ID → title
Columns = Source[columns],
ColumnMap = Table.FromList(Columns, Record.FieldValues, {"ColumnRecord"}),
ExpandedColumns = Table.ExpandRecordColumn(ColumnMap, "ColumnRecord", {"id", "title"}),// Step 3: Extract rows and their cells
Rows = Source[rows],
RowTable = Table.FromList(Rows, Record.FieldValues, {"RowRecord"}),
ExpandedRows = Table.ExpandRecordColumn(RowTable, "RowRecord", {"id", "cells"}),// Step 4: Flatten the 'cells' into a table per row
AddCellTable = Table.AddColumn(ExpandedRows, "CellTable", each Table.FromRecords([cells])),// Step 5: Expand each cell row into individual rows
ExpandedCells = Table.ExpandTableColumn(AddCellTable, "CellTable", {"columnId", "value"}),// Step 6: Join column titles using columnId
Merged = Table.NestedJoin(ExpandedCells, "columnId", ExpandedColumns, "id", "ColDetails", JoinKind.LeftOuter),
ExpandColDetails = Table.ExpandTableColumn(Merged, "ColDetails", {"title"}),// Step 7: Remove unneeded columns (keep id, title, value)
Renamed = Table.RenameColumns(ExpandColDetails, {{"id", "RowID"}, {"title", "ColumnName"}, {"value", "Value"}}),
Cleaned = Table.SelectColumns(Renamed, {"RowID", "ColumnName", "Value"}),// Step 8: Pivot ColumnName → actual columns
Pivoted = Table.Pivot(Cleaned, List.Distinct(Cleaned[ColumnName]), "ColumnName", "Value"),// Optional: sort by RowID
Sorted = Table.Sort(Pivoted, {{"RowID", Order.Ascending}})
in
SortedIf it's ok, please accept the answer as solution!
BBF
4 Replies
RKK Hi! Here's a complete Power Query (M) example that takes the raw Smartsheet JSON structure (with columns and rows), and transforms it into a flat table, where each row from Smartsheet becomes a single row in Power BI — with column names as headers.
let
// Step 1: Load source JSON from the Web API
Source = Json.Document(Web.Contents("https://api.smartsheet.com/2.0/sheets/YOUR_SHEET_ID", [
Headers = [Authorization = "Bearer YOUR_API_KEY"]
])),// Step 2: Create a lookup table of column ID → title
Columns = Source[columns],
ColumnMap = Table.FromList(Columns, Record.FieldValues, {"ColumnRecord"}),
ExpandedColumns = Table.ExpandRecordColumn(ColumnMap, "ColumnRecord", {"id", "title"}),// Step 3: Extract rows and their cells
Rows = Source[rows],
RowTable = Table.FromList(Rows, Record.FieldValues, {"RowRecord"}),
ExpandedRows = Table.ExpandRecordColumn(RowTable, "RowRecord", {"id", "cells"}),// Step 4: Flatten the 'cells' into a table per row
AddCellTable = Table.AddColumn(ExpandedRows, "CellTable", each Table.FromRecords([cells])),// Step 5: Expand each cell row into individual rows
ExpandedCells = Table.ExpandTableColumn(AddCellTable, "CellTable", {"columnId", "value"}),// Step 6: Join column titles using columnId
Merged = Table.NestedJoin(ExpandedCells, "columnId", ExpandedColumns, "id", "ColDetails", JoinKind.LeftOuter),
ExpandColDetails = Table.ExpandTableColumn(Merged, "ColDetails", {"title"}),// Step 7: Remove unneeded columns (keep id, title, value)
Renamed = Table.RenameColumns(ExpandColDetails, {{"id", "RowID"}, {"title", "ColumnName"}, {"value", "Value"}}),
Cleaned = Table.SelectColumns(Renamed, {"RowID", "ColumnName", "Value"}),// Step 8: Pivot ColumnName → actual columns
Pivoted = Table.Pivot(Cleaned, List.Distinct(Cleaned[ColumnName]), "ColumnName", "Value"),// Optional: sort by RowID
Sorted = Table.Sort(Pivoted, {{"RowID", Order.Ascending}})
in
SortedIf it's ok, please accept the answer as solution!
BBF
Hi RKK ,
As we have not received a response from you yet, I would like to confirm whether you have successfully resolved the issue or if you require further assistance.
If the issue has been resolved, please mark the helpful reply as a "solution" to indicate that the question has been answered and to assist others in the community.
Thank you for your cooperation. Have a great day.Hi RKK ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.Hi RKK ,
I just wanted to kindly follow up to see if you had a chance to review the previous response provided by us. I hope it was helpful. If yes, please Accept the answer so that it will be helpful to others to find it quickly.
Thank you.
