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"
Thanks Vijay_A_Verma
How do I use this in the dataset ? What variables need to be changed ?
Its part of a table called V_S_Requests and the Column is called ‘Item Description’.
Can you please advise ?
Open your table in Power Query - Home - Advanced Editor
Copy the source line from there in a notepad
Remove everything from Advanced Editor
Now, paste the below code in Advanced Editor
Replace the source line in Advanced Editor with source line copied earlier (ensure comma at end is there)
OK
(Excel containing above is uploaded to - https://1drv.ms/x/s!Akd5y6ruJhvhuTtwNhgIbYNstKdf?e=cFm4Gi )
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}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Item Description.2"}, {{"Total", each List.Sum([Item Description.1]), type nullable number}}), #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Item Description.2", "Item Description"}}) in #"Renamed Columns"Vijay_A_Verma It does not seem to be working and says table not found. Error : The column 'Item Description' of the table wasn't foundThe source is a SQL server. Does that matter ?
Replace Source with dbo_V_S_Requests in #"Split Column by Delimiter" line.
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.