Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sandeeprave
Helper I
Helper I

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 

 

sandeeprave_0-1648187417708.png

 

 

@amitchandak 

 

1 ACCEPTED SOLUTION

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"

View solution in original post

21 REPLIES 21
sandeeprave
Helper I
Helper I

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 ?

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"

 

@Vijay_A_Verma 

 

I have tried that but its giving an error

sandeeprave_0-1649058903329.png

 

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. 

Thanks for that hint. I think I have figured out why that happened. It was because the column had an extra space.

@Vijay_A_Verma 

 

I have noticed that this fails with the error as shown below. This happens if there are two entries for example : One showing 1 Dock / Monitor and the other showing 1 Monitor / 1 Dock. How can we workaround this ?

 

 

sandeeprave_0-1656031125679.png

 

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 ?

 

sandeeprave_0-1648195982905.pngsandeeprave_1-1648196010878.png

 

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

@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 )

sandeeprave_1-1648240559875.png

 

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'. 

 

sandeeprave_0-1649029598910.png

 

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

sandeeprave_0-1649047477295.png

 

In place of V_S_Requests, your previous step name should come. In my case, previous step was Source. In your case also, it may be Source.

If you still have problem, you will need to paste your statement prior to Filtered Rows.

@Vijay_A_Verma 

This is what I added

 

sandeeprave_1-1649047734228.png

 

Insert this statement after source

#"Filtered Rows" = Table.SelectRows(Source, each ([STATUSNAME] = "Awaiting Item"))

 

@Vijay_A_Verma  - It still doesnt seem to work. It is still picking up numbers from fields that have STATUSNAME not equal to 'Awaiting Item'

 

sandeeprave_2-1649052539997.png

 

 

 

 

 

Make #"Filtered Rows" as third row and in place of Source in that use dbo_V_S_Requests.

Hence first statement will be Source, second will be dbo_V_S_Requests and third will be

#"Filtered Rows" = Table.SelectRows(dbo_V_S_Requests, each ([Status] = "Awaiting Item")),

 

@Vijay_A_Verma  I have tried that and it still shows items which are not with 'Awaiting Item' STATUSNAME

 

sandeeprave_0-1649054364722.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors