Extract specific rows

[koorosh]

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.

Type	path	size
folder	C:\users\koorosh	1200
file	C:\users\koorosh\A	500
folder	C:\users\koorosh\AA	200
file	C:\users\Mike\B	10
folder	C:\users\Mike	2700
folder	C:\users\Mike\BB	800

Type	path	size
folder	C:\users\koorosh	1200
folder	C:\users\Mike	2700

[Fowmy]

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

________________________

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

[Ashish_Mathur · Accepted solution]

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.

[PhilipTreacy]

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"

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.