Forum Discussion
List.Zip Returning an error
I have a column of my table containing only lists, the column contains 200+ rows and the lists them self are also 200+ digits. Ultimately I want to sum the columns of my table, so I may not be using the most efficient method by List.Zip (if you know a method to sum a large amount of columns that will work as well) but I am newer to Power Query. The list is mostly null values due to the way I gather the data (not changeable), but each row contains at least 1 data point that is not null, which makes analyzing the data difficult. When I attempt to use List.Zip I get the follow error:
Expression.Error: We cannot convert the value null to type List.
Details:
Value=
Type=[Type]
Any Advice or corrections I need to make?
Please Disregard this post, and view https://community.fabric.microsoft.com/t5/Power-Query/Sum-all-individual-Columns-for-large-data-sets/m-p/4649367#M149589 where I describe the problem and data set in a better fashion.
Hi Rick_S137 ,
Has the issue been resolved on your end? If so, please share your solution and mark it as "Accept as Solution." This will assist others in the community who are dealing with similar problems and help them find a solution more quickly.
Thank you.
11 Replies
That's not at all what List.Zip is for. You should append these lists instead of zipping them, then filter them to throw out the blanks, and then List.Sum them.
Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.List.Sum(
List.Transform(
List.RemoveItems(
List.Combine(List.Transform(
Table.ColumnNames(Source), each Table.Column(Source, _))),
{"null"}
),
each Int64.From(_)
)
)let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSwYJjdaKV8kpzcoAcgtQAqzXEotAQlYlNCdXdaEhjQ9HNH0JRNHjUxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t, E = _t, F = _t]), l = List.Sum(List.Transform(List.RemoveItems(List.Combine(List.Transform(Table.ColumnNames(Source), each Table.Column(Source,_))),{"null"}),each Int64.From(_))) in lHow to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.
Hi Rick_S137
Could you clarify what your table currently looks like, and the end result you want?
Should the result be a table or a scalar value? I'm not clear on what you mean by "sum the columns".
From your description, I would be looking at using List.Combine and List.Sum, along with List.RemoveNulls.
I don't think List.Zip is needed here from your description.
Regarding the error you are geting, it appears to be because you are passing a null value within the "list of lists" argument that List.Zip takes.
Hi OwenAuger
My table comes from a CSV, and lookes like this:
The columns are very long, and contain a large amount of nulls, but do contain True/Falses which I have made 1/0's through change type. I want to total the amount of 1's that appears in each column so my end result will be each column name with a number representing the sum of the column for example "Column Name: 10"
For some more context my columns are so long I cannot transpose the data set without excel essentially freezing. A List is nice, but I need the 1's to be traceable back to the column they originate from (indexability) and when I take a list it records a row. The issue with list.remove nulls is i lose the idexability and no longer know which column a value belongs to. My goal was List.Zip will rearrange the lists from a row to a list of containing all values from the original column, after which I figured I would be able to list.sum.
List.Combine the Table.ColumnNames, then List.RemoveNulls and then List.Sum
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi I have posted a much better explanation and included a link to it in the first post. Thank you for your suggestions
Hi Rick_S137 , another solution you could look at. I'll attach the images of the output and the M code. The source used is file from the file attached by you below. Thanks
Here is the code used:
let
Source = Excel.CurrentWorkbook(){[Name="Table4"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"abc_X_123.stat.energize", Int64.Type}, {"abc_X_456.stat.energize", Int64.Type}, {"abc_X_789.stat.energize", Int64.Type}, {"abc_Y_123.stat.energize", Int64.Type}, {"abc_Z_123.stat.energize", Int64.Type}, {"abc_Z_456.stat.energize", Int64.Type}}),
Transposed = Table.FromRows(Table.ToColumns(Table.DemoteHeaders(#"Changed Type"))),
Delimit = Table.TransformColumns(Transposed, {{"Column1", each Text.BetweenDelimiters(_, "_", "_"), type text}}),
#"Grouped Rows" = Table.Group(Delimit, {"Column1"}, {{"All", each List.Sum ( List.RemoveNulls ( List.Combine ( List.Skip ( Table.ToColumns( _ ) ) ) ) )
}}),
Table = Table.PromoteHeaders(Table.FromRows(Table.ToColumns(#"Grouped Rows")))
in
TableHi Rick_S137 ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.Hi Rick_S137 ,
Has the issue been resolved on your end? If so, please share your solution and mark it as "Accept as Solution." This will assist others in the community who are dealing with similar problems and help them find a solution more quickly.
Thank youHi Rick_S137 ,
Has the issue been resolved on your end? If so, please share your solution and mark it as "Accept as Solution." This will assist others in the community who are dealing with similar problems and help them find a solution more quickly.
Thank you.
