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

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    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