Forum Discussion
data with different lengths
Hello,
I'm using data from a NoSQL source, which implies that the lines have different lenghts.
Let me explain : I have several lines, and each line contains several values, not the same number. So some of my lines contain "null" values while some other contains more values, as in the following scheme:
line1 | A | 1673 | B | 3445 | C | 2290 | A | 3342 | 10750 | null | null | null | null
line2 | B | 7894 | B | 8594 | A | 5765 | 16434 | null | null | null | null | null | null
line3 | A | 2593 | C | 1335 | B | 3456 | A | 5567 | 12951 | null | null | null | null
line4 | A | 1493 | C | 1078 | B | 2290 | C | 9780 | A | 1673 | B | 3445 | 19759
What I need is to take the last value before null, and move it to a new column, added at the end of my data.
In the example with the data below, I woul like to have the following result:
line1 | A | 1673 | B | 3445 | C | 2290 | A | 3342 | null | null | null | null | null | 10750
line2 | B | 7894 | B | 8594 | A | 5765 | null | null | null | null | null | null | null | 16434
line3 | A | 2593 | C | 1335 | B | 3456 | A | 5567 | null | null | null | null | null | 12951
line4 | A | 1493 | C | 1078 | B | 2290 | C | 9780 | A | 1673 | B | 3445 | null | 19759
Would you have any idea of how to perform it ?
Many thanks
Hi Anonymous ，
Would you please try to refer to the following m-query to get the last null value from your row:
let Source = Excel.Workbook(File.Contents("C:\Users\dedmond\Desktop\test.xlsx"), null, true), Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Sheet2_Sheet,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", type any}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", Int64.Type}}), #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"), #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type text}, {"Column12", type any}, {"Column13", type any}}), #"Transposed Table" = Table.Transpose(#"Changed Type1"), Output = List.Accumulate(Table.ToColumns(#"Transposed Table"), {}, (s, a) => s & {List.Last(List.RemoveNulls(a))}), #"Converted to Table" = Table.FromList(Output, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Promoted Headers" = Table.PromoteHeaders(#"Converted to Table", [PromoteAllScalars=true]), #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column13", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 0, 1) in #"Added Index"And append it with your original query by adding index column.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
1 Reply
Hi Anonymous ，
Would you please try to refer to the following m-query to get the last null value from your row:
let Source = Excel.Workbook(File.Contents("C:\Users\dedmond\Desktop\test.xlsx"), null, true), Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data], #"Changed Type" = Table.TransformColumnTypes(Sheet2_Sheet,{{"Column1", type text}, {"Column2", Int64.Type}, {"Column3", type text}, {"Column4", Int64.Type}, {"Column5", type text}, {"Column6", Int64.Type}, {"Column7", type any}, {"Column8", Int64.Type}, {"Column9", type any}, {"Column10", Int64.Type}, {"Column11", type text}, {"Column12", Int64.Type}, {"Column13", Int64.Type}}), #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type"), #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type any}, {"Column3", type text}, {"Column4", type any}, {"Column5", type text}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type text}, {"Column12", type any}, {"Column13", type any}}), #"Transposed Table" = Table.Transpose(#"Changed Type1"), Output = List.Accumulate(Table.ToColumns(#"Transposed Table"), {}, (s, a) => s & {List.Last(List.RemoveNulls(a))}), #"Converted to Table" = Table.FromList(Output, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Promoted Headers" = Table.PromoteHeaders(#"Converted to Table", [PromoteAllScalars=true]), #"Changed Type2" = Table.TransformColumnTypes(#"Promoted Headers",{{"Column13", Int64.Type}}), #"Added Index" = Table.AddIndexColumn(#"Changed Type2", "Index", 0, 1) in #"Added Index"And append it with your original query by adding index column.
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai