Forum Discussion
Need help with M-Query in Advanced editor
Hi Community,
I have Web As data source report . After working on m-query in advanced editor and applying lot of transformation steps (Combined list, Converting list to table, expanding columns etc) , i found my data to be like this .(shown in screen shot).i do not know why i m getting "list" as values for OS EOS date column. For few rows ,data is coming up properly as you see it in screen shot(values like 11/30/2020 ) .
I have attached m query which i used in case if it helps to resolve the issue .
CombinedList=
List.Combine(
List.Generate(
()=>[res = FnGetOnePage(url)],
each [res][Data]<>null,
each [next_url= [res][Next], res = FnGetOnePage(next_url)],
each [res][Data])),
#"Converted to Table" = Table.FromList(CombinedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"App ID", "App Name", "HW Vendor", "HW/VM Container EOS Date", "HW/VM Container Model", "OS", "OS EOS Date", "Server Name", "VM Host HW EOS Date", "VM Host HW Model"}, {"App ID", "App Name", "HW Vendor", "HW/VM Container EOS Date", "HW/VM Container Model", "OS", "OS EOS Date", "Server Name", "VM Host HW EOS Date", "VM Host HW Model"})
in
#"Expanded Column1"
Hi, Anonymous
Based on your description, I created data to reproduce your scenario.
Source:
= Table.FromRecords( { [Data={null,#date(2020,1,1)}], [Data={null,#date(2020,1,2)}], [Data={null,#date(2020,1,3)}], [Data={null,#date(2020,1,4)}], [Data={null,#date(2020,1,5)}], [Data=#date(2020,1,6)], [Data=#date(2020,1,7)] } )You may add a new step as below.
= Table.TransformColumns(Source, {"Data",each if _ is list then List.Max( List.RemoveNulls(_) ) else if _ is date then _ else null } )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 Replies
Hi, Anonymous
Based on your description, I created data to reproduce your scenario.
Source:
= Table.FromRecords( { [Data={null,#date(2020,1,1)}], [Data={null,#date(2020,1,2)}], [Data={null,#date(2020,1,3)}], [Data={null,#date(2020,1,4)}], [Data={null,#date(2020,1,5)}], [Data=#date(2020,1,6)], [Data=#date(2020,1,7)] } )You may add a new step as below.
= Table.TransformColumns(Source, {"Data",each if _ is list then List.Max( List.RemoveNulls(_) ) else if _ is date then _ else null } )Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous , can you share the sample data inside the list.
Hi Amit,
Below shown are 2 different values in List . We do not want "null" values . But i want to dynamically remove them thru m query . When i am trying to filter those null values and remove them, i do not see other valid records. So i am thinking this should be done through m - query and i am not getting anything.
Thanks
