Forum Discussion

koorosh's avatar
koorosh
Post Partisan
5 years ago
Solved

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...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    5 years ago

    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.

Fowmy's avatar
Fowmy
Super User
5 years ago

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