Need help with M-Query in Advanced editor

[Anonymous]

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"

[v-alq-msft · Accepted solution]

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]

Thanks Allan,

This really helped me a lot to sort out the issue. 

G Venkatesh 

[amitchandak]

@Anonymous , can you share the sample data inside the list.

[Anonymous]

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

[Anonymous]

Hi ,

Is there a way to filter out the null values from that list i have shown in screen shot ?

Kindly assist . 

G venkatesh 

[amitchandak]

@Anonymous , the idea is to check if it is a list then take out first not the null value.

@ImkeF , can you help on this

[ImkeF]

Hi @Anonymous ,

if my understanding is correct, you could add a column with the following formula

  if [OS EOS Date] is list then [OS EOS Date]{1}  else [OS EOS Date]

it checks if the content of the column is of type list and if so, take the second element from it. If not, just take the value as it is.