Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Loop through the table in M query

Hello Everyone,

 

I have a below table name as TitleDim, 

 

 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

 

Need Help
  • Jimmy801's avatar
    Jimmy801
    6 years ago

    Hello Anonymous 

     

    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

8 Replies

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    6 years ago

    Hello Anonymous 

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hello Jimmy801 ,

       

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

       

       

      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.

       

       

      Regards

       

      • Jimmy801's avatar
        Jimmy801
        Community Champion
        6 years ago

        Hello Anonymous 

         

        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