Forum Discussion
Reading through Columns
Is there a way PowerBI can analyse this column from the table and tell me in numbers how many docks, how many headsets etc
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"
21 Replies
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)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlLwzc/LLMkvKlbQVzBScMlPzi5WitWJVjJU8EhNTClOLYHyMgMy8vNSoRyQMjAToV0pNhYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Data = _t]), #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Data", Splitter.SplitTextByDelimiter("/", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Data"), #"Trimmed Text" = Table.TransformColumns(#"Split Column by Delimiter",{{"Data", Text.Trim, type text}}), #"Split Column by Delimiter1" = Table.SplitColumn(#"Trimmed Text", "Data", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Data.1", "Data.2"}), #"Added Custom" = Table.AddColumn(#"Split Column by Delimiter1", "Items", each if Text.End([Data.2],1)="s" then Text.TrimEnd([Data.2],"s") else [Data.2]), #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Data.1", Int64.Type}, {"Data.2", type text}, {"Items", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Items"}, {{"Total", each List.Sum([Data.1]), type nullable text}}) 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 ?
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"