Forum Discussion
Reading through Columns
Use below Query
let Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content], #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Item Description", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Item Description"), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Item Description", Text.Trim, type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Item Description", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Item Description.1", "Item Description.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Item Description.1", Int64.Type}, {"Item Description.2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Item Description", each Text.TrimEnd([Item Description.2],"s")), #"Grouped Rows" = Table.Group(#"Added Custom", {"Item Description"}, {{"Total", each List.Sum([Item Description.1]), type nullable number}}) in #"Grouped Rows"
Use below query and put your first 2 lines containing Source and dbo_V_S_Requests where I have mentioned your first 2 lines (remove //your first 2 lines)
let
//Your first 2 lines
#"Filtered Rows" = Table.SelectRows(dbo_V_S_Requests, each ([Status] = "Awaiting Item")),
#"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"Item Description", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Item Description"),
#"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Item Description", Text.Trim, type text}}),
#"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Item Description", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Item Description.1", "Item Description.2"}),
#"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Item Description.1", Int64.Type}, {"Item Description.2", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Item Description", each Text.TrimEnd([Item Description.2],"s")),
#"Grouped Rows" = Table.Group(#"Added Custom", {"Item Description"}, {{"Total", each List.Sum([Item Description.1]), type nullable number}})
in
#"Grouped Rows"
I have tried that but its giving an error
let Source = Sql.Database("AG-Internal", "servicedesk"),
dbo_V_S_Requests = Source{[Schema="dbo",Item="V_S_Requests"]}[Data],
#"Filtered Rows" = Table.SelectRows(dbo_V_S_Requests, each ([STATUSNAME] = "Awaiting Item")), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Filtered Rows", {{"Item Description", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Item Description"), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Item Description", Text.Trim, type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Item Description", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Item Description.1", "Item Description.2"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Item Description.1", Int64.Type}, {"Item Description.2", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Item Description", each Text.TrimEnd([Item Description.2],"s")), #"Grouped Rows" = Table.Group(#"Added Custom", {"Item Description"}, {{"Total", each List.Sum([Item Description.1]), type nullable number}}) in #"Grouped Rows"
In Power BI desktop - Edit Query - Click Navigation step on right side - Now your table will be displayed - Click any cell in your table - CTRL+A to select all - CTRL+C to copy and paste the content in an Excel sheet.
Remove all data except first row which will contain the Column names.
Upload this Excel file to Onedrive / any other file sharing site and share the link here. I want to check your column names.
Thanks for that hint. I think I have figured out why that happened. It was because the column had an extra space.
I have noticed that this fails with the error as shown below. This happens if there are two entries for example : One showing 1 Dock / Monitor and the other showing 1 Monitor / 1 Dock. How can we workaround this ?