cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Loop through the table in M query

Hello Everyone,

 

I have a below table name as TitleDim, 

 table-sample.png

 its dynamic sometimes has 2 rows and sometimes has 3 rows and more. Loop through the table 'Title.Name' and set as column header and value as Value.Attributes.L01 in another table.

 

Below is my try its working but its static,

 = Table.AddColumn(#"Source Table", TitleDim{0}[Title.Name], each TitleDim{0}[Value.Attributes.L01], type text)

 

How can I do it dynamic?

 

Thanks in advance

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Loop through the table in M query

Hello @PowBI_USER_2020 

 

you can use List.Accumulate to accomplish your task. 

Here the complete solution

let
	Source = #table
	(
		{"Title.Name","Value.Attributes.L01"},
		{
			{"Name1","Australia"},	{"Name2","Volkwagen"}
		}
	),
	CellValue = #table
	(
		{"Cell.Value"},
		{
			{"1"},	{"5"}, {"6"}
		}
	),
	ListAccumulate = List.Accumulate
	(
		{0..Table.RowCount(Source)-1},
		CellValue,
		(old,current)=>
		Table.AddColumn(old, Source[Title.Name]{current}, each Source[Value.Attributes.L01]{current} )
	)
in
    ListAccumulate

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

8 REPLIES 8
Super User I
Super User I

Re: Loop through the table in M query

Hello @PowBI_USER_2020 

 

I don't know if I got you right. However I understood that you need to create a new column, adding a table and using the name and value-column as parameter.

Here the complete solution

let
	Source = #table
	(
		{"Title.Name","Value.Attributes.L01"},
		{
			{"Name1","Australia"},	{"Name2","Volkwagen"}
		}
	),
    TitleFromNameAndValue = Table.AddColumn
    (
        Source,
        "TableFromNameAndValue",
        each #table({[Title.Name]}, {{[Value.Attributes.L01]}}),
        type table
    )
in
    TitleFromNameAndValue

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

Re: Loop through the table in M query

Hello @Jimmy801 ,

 

Thanks for your reply, unfortunately this is not my expected output. Below is my expectation. 

 

expectedOutput.png

 

Target table has one column 'Value'. I would like to loop through the below table, and add the Column name as }zPBI_country and value as Australia and so on in Target table.

 

table-sample.png

 

Regards

 

Super User I
Super User I

Re: Loop through the table in M query

Hello @PowBI_USER_2020 

 

this means that if you have 10 rows in your target table and 2 in your table to be looped through, the expected result is a table with 20 rows where the values are duplicated. 

 

Jimmy

Re: Loop through the table in M query

Hi,

 

Initially I have the Target table as below,

TargetTable.png

 

My second table is where looping need to be done

table-sample.png

Outcome as 

exp.output.png

 

Highlighted
Super User I
Super User I

Re: Loop through the table in M query

Hello @PowBI_USER_2020 

 

you can use List.Accumulate to accomplish your task. 

Here the complete solution

let
	Source = #table
	(
		{"Title.Name","Value.Attributes.L01"},
		{
			{"Name1","Australia"},	{"Name2","Volkwagen"}
		}
	),
	CellValue = #table
	(
		{"Cell.Value"},
		{
			{"1"},	{"5"}, {"6"}
		}
	),
	ListAccumulate = List.Accumulate
	(
		{0..Table.RowCount(Source)-1},
		CellValue,
		(old,current)=>
		Table.AddColumn(old, Source[Title.Name]{current}, each Source[Value.Attributes.L01]{current} )
	)
in
    ListAccumulate

 

Copy paste this code to the advanced editor in a new blank query to see how the solution works. If this solution fits your need, copy and past a part of it and implement it in your query.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

View solution in original post

Super User IV
Super User IV

Re: Loop through the table in M query

Hi @PowBI_USER_2020 

 

Please see the attached file with a solution.

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

Re: Loop through the table in M query

Hi @Jimmy801 ,

 

Great. Perfect Solution. You help is much appreciated. Thanks

Super User I
Super User I

Re: Loop through the table in M query

Hello @PowBI_USER_2020 

 

your feedback is much appreciated.

 

Jimmy

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors