Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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   
Need Help
Tips and Tricks
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    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's avatar
Vijay_A_Verma
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

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"