question

kirukim-9614 avatar image
0 Votes"
kirukim-9614 asked kirukim-9614 edited

How to get all the values in the second column that are related to the first one directly or not?

Urgent!!!Need help!
It's been days trying to solve this
I wanna write code(python or M for Power Bi) that will allow me to iterate over those 2 columns and check the values and get all the values from the second column that may have a relationship with the first one directly or not as shown in the second picture.

83823-4.png83852-3.png


power-query-not-supported
4.png (6.0 KiB)
3.png (7.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Appears to be a duplicate of Iterate over a table using M


0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered kirukim-9614 commented

Hi @kirukim-9614

(re. can u please write the M code?)

 let
     // Table for demo.
     Source = Table.FromColumns(
         {
             {"a","a","a","b","b","b","c","c","x","g","w"},
             {"b","c","d","x","y","z","e","f","m","k","g"}
         },
         type table [Calling=text, Called=text]
     ),
     //
     GroupedRows = Table.Group(Source, {"Calling"},
         {"Called", each
             let
                 One = List.Combine(
                     List.Transform([Called], (i)=>
                         Table.SelectRows(Source, each [Calling] = i)[Called]
                     )
                 ),
                 Two = List.Combine(
                     List.Transform(One, (i)=>
                         Table.SelectRows(Source, each [Calling] = i)[Called]
                     )
                 )
             in
                 List.Combine({[Called], One, Two}),
             type {text}
         }
     ),
     ExpandedCalled = Table.ExpandListColumn(GroupedRows, "Called")
 in
     ExpandedCalled

The let...in portion inside the GroupedRows step can be turned into a function. After checking the above does what you expect let me know if you want a version with a function

· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks a lot!! that helps

0 Votes 0 ·

how can I replace the data u entrer white my columns cause when I triend the I get an error

0 Votes 0 ·

@kirukim-9614
- For next times: "I get an error" doesn't help at all as you do not provide the error message
- "How can I replace the data u entrer with my columns": without seeing your current query code I cannot tell you

0 Votes 0 ·

85420-error.png
This is the code that I ve used

 let
     Source = Excel.Workbook(File.Contents("C:\Users\Micro\OneDrive\Bureau\test\test.xlsx"), null, true),
     Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
     #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{
                      {"Calling", type text}, {"Called", type text}}),
     GroupedRows = Table.Group(#"Changed Type", {"Calling"},..


till the end of your code


0 Votes 0 ·
error.png (5.5 KiB)

85439-data.png


ans this is my excel file data

0 Votes 0 ·
data.png (8.1 KiB)
Show more comments
Lz-3068 avatar image
0 Votes"
Lz-3068 answered kirukim-9614 commented

Hi @kirukim-9614

The following approach should do what you want:

1/ Group your table by [Calling], then inside the group:
a) ListOne = For each [Called], SelectRows from Source where [Calling] = [Called], then return the [Called] field as a List and combine that List
b) ListTwo = For each item in ListOne, SelectRows from Source where [Calling] = ListItem, then combine that List
c) Combine Lists: [Called], ListOne, ListTwo
2/ Expand the combined Lists as new rows

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

can u please write the M code?

0 Votes 0 ·
HerbertSeidenberg-6295 avatar image
0 Votes"
HerbertSeidenberg-6295 answered HerbertSeidenberg-6295 commented
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

See Wikipedia "Linked List"
Your unknown real data may be a
Singly/Doubly/Multiply and/or Circular Linked List
with Sentinel Nodes/List Handles and/or Pointers,
each type requiring a unique program for analysis.
My solution assumes a Singly Linked List with one List Handle.
I added a calculated tree diagram and a tweaked LZ program.
Same file link.

1 Vote 1 ·

In my case i used python to do it , this is the code that i used :

'dataset' holds the input data for this script

import pandas as pd
new_data = pd.DataFrame()

data_called = pd.Series([])
data_calling= pd.Series([])

for i in range(0,len(dataset['Calling'])):
for j in range(0,len(dataset['Called'])):
if dataset['Called'][i]== dataset['Calling'][j]:
data_calling[j]= dataset['Calling'][i]
data_called[j]= dataset['Called'][j]


new_data.insert(0, "Calling", data_calling)
new_data.insert(1, "Called", data_called)

final_result = pd.concat([dataset, new_data], ignore_index=True)
final_result = final_result.sort_values("Calling")


0 Votes 0 ·

new_data = pd.DataFrame()
datatest = {
"Calling":""
,
"Called" : ""
}
for i in range(0,len(data['Calling'])):
for j in range(0,len(data['Called'])):
if data['Called'][i]== data['Calling'][j]:
datatest["Calling"] += data['Calling'][i] + "\n"
` datatest["Called"] +=data['Called'][j] + "\n"

datatest["Calling"] = datatest["Calling"].split('\n')
datatest["Called"] = datatest["Called"].split('\n')
datatest = pd.DataFrame.from_dict(datatest)
result = pd.concat([datatest, data])

0 Votes 0 ·

Your data has three generations, not two.

0 Votes 0 ·
kirukim-9614 avatar image kirukim-9614 HerbertSeidenberg-6295 ·

Didn't get you but finally I used this code with python :

new_data = pd.DataFrame()
datatest = {
"Calling":""
,
"Called" : ""
}
for i in range(0,len(data['Calling'])):
for j in range(0,len(data['Called'])):
if data['Called'][i]== data['Calling'][j]:
print("*")
print(data['Called'][i]== data['Calling'][j])
datatest["Calling"] += data['Calling'][i] + "\n"
# print("calling",data_calling[j])
datatest["Called"] +=data['Called'][j] + "\n"

datatest["Calling"] = datatest["Calling"].split('\n')
datatest["Called"] = datatest["Called"].split('\n')
datatest = pd.DataFrame.from_dict(datatest)
result = pd.concat([datatest, data])

0 Votes 0 ·
Lz-3068 avatar image
0 Votes"
Lz-3068 answered kirukim-9614 commented

Hey @kirukim-9614

You didn't tell me the truth :) The issue isn't with the #Changed Type step
What you probably didn't update in the code I provided is the reference to the Source in the 2 Table.SelectRows inside the GroupedRows step. Try the following:

 let
     Source = Excel.Workbook(
     File.Contents("C:\Users\Micro\OneDrive\Bureau\test\test.xlsx"),
     null, true
     ),
     Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
     #"Changed Type" = Table.TransformColumnTypes(Table1_Table,
         {
                 {"Calling", type text}, {"Called", type text}}
     ),
     GroupedRows = Table.Group(#"Changed Type", {"Calling"},
          {"Called", each
              let
                  One = List.Combine(
                      List.Transform([Called], (i)=>
                          Table.SelectRows(#"Changed Type", each [Calling] = i)[Called]
                      )
                  ),
                  Two = List.Combine(
                      List.Transform(One, (i)=>
                          Table.SelectRows(#"Changed Type", each [Calling] = i)[Called]
                      )
                  )
              in
                  List.Combine({[Called], One, Two}),
              type {text}
          }
      ),
      ExpandedCalled = Table.ExpandListColumn(GroupedRows, "Called")
 in
     ExpandedCalled
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

kirukim-9614 avatar image
0 Votes"
kirukim-9614 answered kirukim-9614 commented

It'll be nice from you to add the case of A with A and B with B cause I need them as a root in my network otherwise you really helped me.Thank you!!

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@kirukim-9614
I don't understand what you're asking - sorry. Try to re-explain or (better) upload a couple of pictures showing what you have and what you expect

0 Votes 0 ·

it's down

0 Votes 0 ·
kirukim-9614 avatar image
0 Votes"
kirukim-9614 answered kirukim-9614 edited

86210-capture.png

In addition to the other values, I wanna have each value from Calling with itself as shown in the picture, I gave some examples



extra.png (11.0 KiB)
capture.png (7.0 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered

@kirukim-9614

Use the below code. Note that I reorganized the query so you don't have to make changes to the Table.SelectRows inside the GroupedRows

 let
     Source =
         let
             Source = Excel.Workbook(
                 File.Contents("C:\Users\Micro\OneDrive\Bureau\test\test.xlsx"),
                 null, true
             ),
             Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data]
         in
             Table.TransformColumnTypes(Table1_Table,
                 {
                 {"Calling", type text}, {"Called", type text}}
             ),
     GroupedRows = Table.Group(Source, {"Calling"},
         {"Called", each
             let
                 Exception = List.First([Calling]),
                 One = List.Combine(
                     List.Transform([Called], (i)=>
                         Table.SelectRows(Source, each [Calling] = i)[Called]
                     )
                 ),
                 Two = List.Combine(
                     List.Transform(One, (i)=>
                         Table.SelectRows(Source, each [Calling] = i)[Called]
                     )
                 )
             in
                 List.Combine({
                 {Exception}, [Called], One, Two}),
             type {text}
         }
     ),
     ExpandedCalled = Table.ExpandListColumn(GroupedRows, "Called")
 in
     ExpandedCalled


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

kirukim-9614 avatar image
0 Votes"
kirukim-9614 answered kirukim-9614 edited

Thank you @Lz-3068
If I've other columns that I wanna show In this new table after those transformations?? how can I use them in the same code cause this excel file contains only the idea It's not the real data, This is my code and my main columns are APPELANT_KEY = Calling , APPELE_KEY = Called in the exemple :

 let
      Source =
          let
              Source = Csv.Document(File.Contents("C:\Users\Micro\OneDrive\Bureau\CrossRef\CROSS_REF.csv"),
              [Delimiter=";", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]),
              #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
          in
               Table.TransformColumnTypes(#"Promoted Headers",{
                 {"APPELANT_KEY", type text}, {"APPELE_KEY", type text}, {"LOC", type text}, {"CONSTR", type text}, {"TYPE_RESOLVE", type text}, {"Type", type text}, {"TYPE_APPELANT", type text}, {"TYPE_APPELE", type text}, {"CONCAT", type text}, {"TYPE_RESOLVE_PREFERENCE", type text}, {"TYPE_RESOLVE_TRANSFORM", type text}, {"APPELANT_LIBELE", type text}, {"APPELE_LIBELE", type text}, {"MyPath", type text}, {"Index", Int64.Type}, {"APPELANT_KEY2", type text}, {"APPELE_KEY2", type text}}),
      GroupedRows = Table.Group(Source, {"APPELANT_KEY"},
          {"APPELE_KEY", each
              let
                  Exception = List.First([APPELANT_KEY]),
                  One = List.Combine(
                      List.Transform([APPELE_KEY], (i)=>
                          Table.SelectRows(Source, each [APPELANT_KEY] = i)[APPELE_KEY]
                      )
                  ),
                  Two = List.Combine(
                      List.Transform(One, (i)=>
                          Table.SelectRows(Source, each [APPELANT_KEY] = i)[APPELE_KEY]
                      )
                  )
              in
                  List.Combine({
                  {Exception}, [APPELE_KEY], One, Two}),
              type {text}
          }
      ),
      ExpandedCalled = Table.ExpandListColumn(GroupedRows, "APPELE_KEY")
  in
      ExpandedCalled


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

kirukim-9614 avatar image
0 Votes"
kirukim-9614 answered kirukim-9614 edited

And If we have other steps before the "GroupedRows" should we use the last step instead of "Source"? Note that I wanna keep other columns that I have In my table and I applied some transformations on them.

 GroupedRows = Table.Group(Source, {"Calling"},
          {"Called", each
              let
                  Exception = List.First([Calling]),
                  One = List.Combine(
                      List.Transform([Called], (i)=>
                          Table.SelectRows(Source, each [Calling] = i)[Called]
                      )
                  ),
                  Two = List.Combine(
                      List.Transform(One, (i)=>
                          Table.SelectRows(Source, each [Calling] = i)[Called]
                      )
                  )
              in
                  List.Combine({
                  {Exception}, [Called], One, Two}),
              type {text}
          }
      ),



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Lz-3068 avatar image
0 Votes"
Lz-3068 answered kirukim-9614 commented

Hi @kirukim-9614
This sounds like a never ending story... IMHO you should organize your queries as follow (feel free to rename them later)

GetData

 let
     Source = Csv.Document(
         File.Contents("C:\Users\Micro\OneDrive\Bureau\CrossRef\CROSS_REF.csv"),
         [Delimiter=";", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]
     ),
     PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
     ChangedTypes = Table.TransformColumnTypes(PromotedHeaders,
         {
             {"APPELANT_KEY", type text}, {"APPELE_KEY", type text}, {"LOC", type text}, {"CONSTR", type text},
             {"TYPE_RESOLVE", type text}, {"Type", type text}, {"TYPE_APPELANT", type text}, {"TYPE_APPELE", type text},
             {"CONCAT", type text}, {"TYPE_RESOLVE_PREFERENCE", type text}, {"TYPE_RESOLVE_TRANSFORM", type text},
             {"APPELANT_LIBELE", type text}, {"APPELE_LIBELE", type text}, {"MyPath", type text}, {"Index", Int64.Type},
             {"APPELANT_KEY2", type text}, {"APPELE_KEY2", type text}
         }
     )
     // Any other step before Grouping [APPELANT_KEY] (done in QueryResult) should be added below
 in
     ChangedTypes

AddAppele (function):

 (sourceTable as table, innerTable as table, optional newColumn as nullable text) as table =>
 let
     Source = sourceTable,
     NewColumnName = if newColumn is null then "NEW_COLUMN_APPELE" else newColumn,
     AddedListCalled = Table.AddColumn(innerTable, NewColumnName, each
         let
             Exception = List.First(innerTable[APPELANT_KEY]),
             One = List.Combine(
                 List.Transform(innerTable[APPELE_KEY], (i)=>
                     Table.SelectRows(Source, each [APPELANT_KEY] = i)[APPELE_KEY]
                 )
             ),
             Two = List.Combine(
                 List.Transform(One, (i)=>
                     Table.SelectRows(Source, each [APPELANT_KEY] = i)[APPELE_KEY]
                 )
             )
         in
             List.Combine({
                 {Exception}, innerTable[APPELE_KEY], One, Two}),
         type {text}
     ),
     ExpandedList = Table.ExpandListColumn(AddedListCalled, NewColumnName)
 in
     ExpandedList

QueryResult

 let
     Source = GetData,
     GroupedAppelant = Table.Group(Source, {"APPELANT_KEY"},
         {"TBL_APPELANT", each AddAppele(Source, _, "THE_NEW_COLUMN_NAME"), type table}
     ),
     CombinedTables = Table.Combine(GroupedAppelant[TBL_APPELANT]),
     ReorderedColumns = Table.SelectColumns(CombinedTables,
         List.FirstN(Table.ColumnNames(CombinedTables),2)
         & {List.Last(Table.ColumnNames(CombinedTables))}
         & List.RemoveLastN(List.Skip(Table.ColumnNames(CombinedTables),2),1)
     )
 in
     ReorderedColumns

Implemented in this workbook (don't forget to change the path to the csv file) + added another option (QueryResult2)


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.