cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Usinghal
Helper I
Helper I

Power query

Hey Team,

 

Is there any method in power query to find for a particular key word for the complete row instead of just searching for a particular column 

 

If there is any method for this then please suggest.

 

Thanks

1 ACCEPTED SOLUTION

I would do it differently.

 

edhans_0-1633543195090.png

I did this:

  1. Seleccted the first 3 columns, then unpivoted other columns
  2. Filtered the "a" out of the Values column
  3. Grouped by the first 3 columns and did a CountRows.

I get this:

edhans_1-1633543278046.png

If you need the original data again, just merge it with the source step.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUUoE4lQoDcGxOtFKVVVVQDaUhMoUgDFItqICpBNCwCXAkrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, #"1/1/2021" = _t, #"1/2/2021" = _t, #"1/3/2021" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1", "Column2", "Column3"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "p")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Column1", "Column2", "Column3"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Column1", "Column2", "Column3"}, #"Grouped Rows", {"Column1", "Column2", "Column3"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"})
in
    #"Expanded Grouped Rows"

 

 

would not recommend the final merge steps though. It is generally bad practice to leave dates in columns. They should be rows, both in Power Query and DAX.

But, it looks like this. This will work with any number of date columns.

edhans_2-1633543553670.png

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

11 REPLIES 11
edhans
Super User
Super User

Try this @Usinghal - You can convert a record to a list, then search it. If it returns the text you are looking for, it will count the returns and return 0 if nothing was found, or 1 or more if it was found.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUUoC4mSlWJ1opRQgKxWI05RiYwE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t]),
    #"Added Custom" = 
        Table.AddColumn(
            Source, 
            "Custom", 
            each 
                List.Count(
                    List.FindText(
                        Record.ToList(Source{_}),
                        "a")
                    )
                )
in
    #"Added Custom"

 

I searched for the letter "a" here. I'd need more data to see what you are exactly trying to accomplish.

edhans_0-1633540911966.png

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Usinghal_0-1633542505957.png

I have attached the screenshot above, In this, I have to calculate the number of P's in date columns for every row and these date columns is dynamic, it will change from list to list

I would do it differently.

 

edhans_0-1633543195090.png

I did this:

  1. Seleccted the first 3 columns, then unpivoted other columns
  2. Filtered the "a" out of the Values column
  3. Grouped by the first 3 columns and did a CountRows.

I get this:

edhans_1-1633543278046.png

If you need the original data again, just merge it with the source step.

 

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WqlDSUUoE4lQoDcGxOtFKVVVVQDaUhMoUgDFItqICpBNCwCXAkrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, #"1/1/2021" = _t, #"1/2/2021" = _t, #"1/3/2021" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Column1", "Column2", "Column3"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = "p")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Column1", "Column2", "Column3"}, {{"Count", each Table.RowCount(_), Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(Source, {"Column1", "Column2", "Column3"}, #"Grouped Rows", {"Column1", "Column2", "Column3"}, "Grouped Rows", JoinKind.LeftOuter),
    #"Expanded Grouped Rows" = Table.ExpandTableColumn(#"Merged Queries", "Grouped Rows", {"Count"}, {"Count"})
in
    #"Expanded Grouped Rows"

 

 

would not recommend the final merge steps though. It is generally bad practice to leave dates in columns. They should be rows, both in Power Query and DAX.

But, it looks like this. This will work with any number of date columns.

edhans_2-1633543553670.png

 

How to use M code provided in a blank query:
1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done
5) See this article if you need help using this M code in your model.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Thank you, It helped me to get the required output

Excellent @Usinghal - whenver I have variable columns, Unpivot Other Columns is one of my first go-to tools. Hope the rest of your project goes well.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hey,

 

Suppose I have 500 tables and I need to expand those tables and need to apply the same query to all those tables, So how can we do that

And when we expand those tables, they should appear as different query

You can write a custom function and apply it to 500 queries, or you can bring in 500 tables and combine as one. 

But you cannot bring in 500 tables and press a button and create 500 queries. Why in the world would you want 500 tables in your model? That would be a DAX nightmare. You should bring them into 1 table, but perhaps with some source name next to them, then you could filter each of them.

But this is really another thread.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Yes, after unpivoting it, the calculations became so easy to be done and I skipped that merging part, because some errors were there while expanding those

watkinnc
Super User
Super User

Why yes there is! It's Table.FindText(Table, "TextToFind")

 

This will return all rows where that string appears.

 

--Nate


I’m usually answering from my phone, which means the results are visualized only in my mind. You’ll need to use my answer to know that it works—but it will work!!

I tried this but it not giving me the required output.

 

Ok, but your response isn't terribly revealing.

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors