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 amitchandak
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"
Vijay_A_Verma Thanks. Is it possible to identify equipment based on type (For Example - Show Dock as one row rather than Dock and Docks )
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"Vijay_A_Verma If I wanted the Count to be based on a Column in the table, how do I do that ? For example - I want it to Count only if the Column field is 'Awaiting Item' in a Column called 'STATUS'.
Just a filter statement after Source will do the trick.
let Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content], #"Filtered Rows" = Table.SelectRows(Source, 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"Vijay_A_Verma I am getting an error though
But 'STATUSNAME' is the name of the column in V_S_Requests though