Extract 3 tables from the source table by specific text in cell

Question

I have a source table with more or less this format :

And I search how to extract the data for each section by using only the data from 'Name' column. But like i'm pretty new to PowerQuery and PowerBi, I don't find the right command to reach my goal

The first possibillity is to add a new column and identify each line by the title like this :

Or the second possibility is to create 3 new table for each title and separate the data like this :

Thanks

nop, it's just an example, you need to read free text instead — MonkeyJLuffy
– MonkeyJLuffy, Commented Oct 28, 2022 at 16:01

horseyride · Accepted Answer · 2022-10-28 16:37:35Z

2

Add a custom column that checks the Value1 column for a null, and if its a null, then return the value in Name

= if [Value 1]=null then [Name] else null

then right click and fill down that new column, and apply a filter to it

let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Added Custom" = Table.AddColumn(Source, "Custom", each if [Value 1]=null then [Name] else null),
#"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"})
// add row here to optionally filter on Custom column
in  #"Filled Down"

Or just have one query and create more queries that have specific filters like this one

let Source  = Table.SelectRows(OtherQueryNameHere, each [Custom] = "m") in Source

edited Oct 28, 2022 at 16:37

answered Oct 28, 2022 at 16:03

horseyride

21.8k3 gold badges15 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MonkeyJLuffy Over a year ago

It's a simple example, but there are also value in Value1 column, I'm looking to manage it by using only the name column

horseyride Over a year ago

Then find something unique about the name column and use that instead of a null in the value column. Does it contain an underscore? Test for that. Alpha in a column of numerics? Test for that. Are sections separated by a blank row? Test for that. Provide some specific here. If you don't give us a good sample input, the recommended solution will not match the need

Collectives™ on Stack Overflow

Extract 3 tables from the source table by specific text in cell

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related