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"
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 ?

Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

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"

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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 ?

     

     

    • Vijay_A_Verma's avatar
      Vijay_A_Verma
      Most Valuable Professional
      4 years ago

      Replace Source with dbo_V_S_Requests in #"Split Column by Delimiter" line.

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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 )