cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
koorosh
Helper IV
Helper IV

Extract specific rows

Hello All,

The first table represents all folders, subfolders and files along with their sizes for each user. If we want to find how much space each user used, we should extract the row just for the main upper folder for each user. For instance for Koorosh user, we should extract row with path c:\users\koorosh.

How we can do it? In other words how we can reach to second table from the first table.

Typepathsize
folderC:\users\koorosh1200
fileC:\users\koorosh\A500
folderC:\users\koorosh\AA200
fileC:\users\Mike\B10
folderC:\users\Mike2700
folderC:\users\Mike\BB

800

 

Typepathsize
folderC:\users\koorosh1200
folderC:\users\Mike2700
1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvPSUktUtJRcraKiSktTi0qjonJzs8vyi/OAAoaGhkYKMXqAJVl5qRiVRQT4wgUN4Upw20aUCFIJW4DfTOzU2NinEC24jYMpAhkijkeC6EGgUyyACmLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, path = _t, size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"path", type text}, {"size", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "folder")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Length([path])-Text.Length(Text.Replace([path],"\",""))),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each [Custom] <= 2)
in
    #"Filtered Rows1"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

3 REPLIES 3
Fowmy
Super User
Super User

@koorosh 
You can do it in Power Query with the simple step by adding a custom column. Paste the following code in blank query and check the steps

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvPSUktUtJRcraKiSktTi0qjonJzs8vyi/OAAoaGhkYKMXqAJVl5qRiVRQT4wgUN4Upw20aUCFIJW4DfTOzU2NinEC24jYMpAhkijkeC6EGgUyyACmLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, path = _t, size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"size", Int64.Type}}),
    #"Trimmed Text" = Table.TransformColumns(#"Changed Type",{{"path", Text.Trim, type text}}),
    #"Filtered Rows" = Table.SelectRows(#"Trimmed Text", each ([Type] = "folder")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each List.Count(Text.Split([path],"\"))-1),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] = 2))
in
    #"Filtered Rows1"

Fowmy_0-1605943588344.png

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Ashish_Mathur
Super User
Super User

Hi,

This M code works

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvPSUktUtJRcraKiSktTi0qjonJzs8vyi/OAAoaGhkYKMXqAJVl5qRiVRQT4wgUN4Upw20aUCFIJW4DfTOzU2NinEC24jYMpAhkijkeC6EGgUyyACmLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, path = _t, size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"path", type text}, {"size", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "folder")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each Text.Length([path])-Text.Length(Text.Replace([path],"\",""))),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each [Custom] <= 2)
in
    #"Filtered Rows1"

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

PhilipTreacy
Super User
Super User

Hi @koorosh 

This code does the job.  Here's a sample PBIX file with the code

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSsvPSUktUtJRcraKiSktTi0qjonJzs8vyi/OAAoaGhkYKMXqAJVl5qRiVRQT4wgUN4Upw20aUCFIJW4DfTOzU2NinEC24jYMpAhkijkeC6EGgUyyACmLBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Type = _t, path = _t, size = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Type", type text}, {"path", type text}, {"size", Int64.Type}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Type] = "folder")),
    #"Added Custom" = Table.AddColumn(#"Filtered Rows", "Custom", each if List.Count(Text.PositionOf([path],"\",3)) = 2 then [path] else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] <> null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"})
in
    #"Removed Columns"

 

folder-size.png

Regards

Phil


If I answered your question please mark my post as the solution.
If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.



Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.


Blog :: YouTube Channel :: Connect on Linkedin


Proud to be a Super User!


Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!