Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
The Below is my scenario
I have a Sharepoint list which is having multiple other lists associated with it. This I can see if I import the list into Power BI using the Sharepoint List Data Connector.
Suppose A is my list and it has B and C as the associated list.
When I import data from List A, the import takes a couple of minutes.
Now if I expand any of my sublists B or C, the data import is becoming very slow. I takes more than 7 hours in my case.
So to avaid this I thought of using the associated lists as separate tables in power bi. Now the limitation that I am facing is,
How do I convert the Sublist as a separate table.?
P.S. For the information, if I expand the needed list to a new query from the sublists for list A, the new query gets created. But this also happens on top of the existing List A
can someone let me know how to handle this?
Solved! Go to Solution.
Hi @Thejeswar
I can reproduce your problem.
Please pay attention to the characters colored.
let
Source = Table1,
#"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromList([Custom1],Record.FieldValues)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"somethng", "DateStart", "DateEnd", "Custom1", "Custom2"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2"}, {"Custom.Column1", "Custom.Column2"})
in
#"Expanded Custom"
The character with the same color should be the same name.
Please look at my pbix for reference.
Best Regards
Maggie
Hi @Thejeswar
To expand the needed list to a new query from the sublists for list A without happening on top of the existing List A, please create a blank query, then in advanced editor, input the following code.
let Source = Table1, #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromList([Custom1],Record.FieldValues)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"somethng", "DateStart", "DateEnd", "Custom1", "Custom2"}), #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2"}, {"Custom.Column1", "Custom.Column2"}) in #"Expanded Custom"
Best Regards
Maggie
Hi,
I did this. But it is giving me error that "cannot convert Value of type Record to Type Table"
What could be the reason?
let Source = #"MySource", #"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromList([Custom1],Record.FieldValues)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"somethng", "DateStart", "DateEnd", "ColumnA", "ColumnB"}), #"Expanded Atr" = Table.ExpandTableColumn(#"Removed Columns", "Atr", {"Id", "Title"}, {"Atr.Id", "Atr.Title"}), #"Removed Errors" = Table.RemoveRowsWithErrors(#"Expanded Atr", {"Atr.Id"}), #"Removed Duplicates" = Table.Distinct(#"Removed Errors", {"Atr.Id"}) in #"Removed Duplicates"
This is my M-Query for reference.
Hi @Thejeswar
I can reproduce your problem.
Please pay attention to the characters colored.
let
Source = Table1,
#"Added Custom" = Table.AddColumn(Source, "Custom", each Table.FromList([Custom1],Record.FieldValues)),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"somethng", "DateStart", "DateEnd", "Custom1", "Custom2"}),
#"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Column1", "Column2"}, {"Custom.Column1", "Custom.Column2"})
in
#"Expanded Custom"
The character with the same color should be the same name.
Please look at my pbix for reference.
Best Regards
Maggie
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |