How to expand list column which has empty data

[ykannan]

Hi Friends,

I am taking data from Firebase DB which has List columns. One of the column data.caxxxx_order_id has empty data and more than one data. 

Below is the code where I need to change to display "" or add new rows for each Order id. For now I have hardcoded to take first value but I need dynamic code to display all order_ids.

let
Source = GoogleBigQuery.Database(),
svcfirestage = Source{[Name="xxxFirebaseDB"]}[Data],
firestore_export_FTxxxx_BI_Schema = svcfirestage{[Name="firestore_export_FTxxxx_BI",Kind="Schema"]}[Data],
FTxxxx_CASE_raw_latest_View = firestore_export_FTxxxx_BI_Schema{[Name="FTxxxx_CASE_raw_latest",Kind="View"]}[Data],
#"Parsed JSON" = Table.TransformColumns(FTxxxx_CASE_raw_latest_View,{{"data", Json.Document}}),
#"Expanded data" = Table.ExpandRecordColumn(#"Parsed JSON", "data", {"c00001_assc_d_id", "c00003_acctrepid", "c00003_acctrepnm", "c00003_coveredbyid", "c00003_coveredbynm", "c00004_acctid", "c00004_acctnm", "cxxxx_acctdistid", "cxxxx_acctdistnm", "cxxxx_casecolor", "cxxxx_caseid", "cxxxx_createdbyid", "cxxxx_createdbyname", "cxxxx_createddate", "cxxxx_do_flag", "cxxxx_enddt", "cxxxx_eventid", "cxxxx_lvl", "cxxxx_notes", "cxxxx_patient_id", "cxxxx_planequipmentflag", "cxxxx_robot", "cxxxx_startdt", "cxxxx_statusid", "cxxxx_timezn", "cxxxx_title", "cxxxx_trigger_end_time", "cxxxx_trigger_start_time", "cxxxx_updatedby", "cxxxx_updatedt", "cxxxx_voidfl", "c7620_item_only_fl", "c9100_compid", "ca101_assc_userids", "ca00003_shared", "ca00003_sharedrepids", "caxxxx_order_id", "caxxxx_sugnsnpis", "caxxxx_surgnids", "caxxxx_surgns", "ca901_procedures", "ca901_proceduresids", "ftxxxx_uploads"}, {"data.c00001_assc_d_id", "data.c00003_acctrepid", "data.c00003_acctrepnm", "data.c00003_coveredbyid", "data.c00003_coveredbynm", "data.c00004_acctid", "data.c00004_acctnm", "data.cxxxx_acctdistid", "data.cxxxx_acctdistnm", "data.cxxxx_casecolor", "data.cxxxx_caseid", "data.cxxxx_createdbyid", "data.cxxxx_createdbyname", "data.cxxxx_createddate", "data.cxxxx_do_flag", "data.cxxxx_enddt", "data.cxxxx_eventid", "data.cxxxx_lvl", "data.cxxxx_notes", "data.cxxxx_patient_id", "data.cxxxx_planequipmentflag", "data.cxxxx_robot", "data.cxxxx_startdt", "data.cxxxx_statusid", "data.cxxxx_timezn", "data.cxxxx_title", "data.cxxxx_trigger_end_time", "data.cxxxx_trigger_start_time", "data.cxxxx_updatedby", "data.cxxxx_updatedt", "data.cxxxx_voidfl", "data.c7620_item_only_fl", "data.c9100_compid", "data.ca101_assc_userids", "data.ca00003_shared", "data.ca00003_sharedrepids", "data.caxxxx_order_id", "data.caxxxx_sugnsnpis", "data.caxxxx_surgnids", "data.caxxxx_surgns", "data.ca901_procedures", "data.ca901_proceduresids", "data.ftxxxx_uploads"}),
#"Expanded data.cxxxx_startdt" = Table.ExpandRecordColumn(#"Expanded data", "data.cxxxx_startdt", {"_seconds"}, {"data.cxxxx_startdt._seconds"}),
#"Expanded data.cxxxx_enddt" = Table.ExpandRecordColumn(#"Expanded data.cxxxx_startdt", "data.cxxxx_enddt", {"_seconds"}, {"data.cxxxx_enddt._seconds"}),
#"Added Custom" = Table.AddColumn(#"Expanded data.cxxxx_enddt", "Start Date", each #datetime(190000,01,01,0,0,0) + #duration(0,0,0,[data.cxxxx_startdt._seconds])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "End Date", each #datetime(190000,01,01,0,0,0) + #duration(0,0,0,[data.cxxxx_enddt._seconds])),

#"Removed Other Columns" = Table.SelectColumns(#"Removed Columns",{"data.cxxxx_caseid", "data.caxxxx_order_id"}),
#"Added Custom2" = Table.AddColumn(#"Removed Other Columns","OrderId", each if List.Count([data.caxxxx_order_id]) >0 then [data.caxxxx_order_id]{0} else "" )
in
#"Added Custom2"

Data:

Case_id  Order_id

c1           

c2          O1

              O2

              O3

Looking for

Case_id  Order_id

c1           

c2          O1

c2          O2

c2          O3

[ykannan]

Can someone please help on this? 

[Dhacd]

Hi @ykannan 

There is a fill-down option in the power query, Can you try that and check.
Note: the cells below c2 should be blank in order for this to work. If it note use the replace option in power query to replace it will null

If this post helps, then please consider accepting it as the solution to help the other members find it more quickly.
Regards,
Atma.

[ykannan]

Hi @Dhacd , Thanks for your reply.
Order_id column has list data. Fill_down did not work for that. 
The list is empty for some rows and some has value. When I expand and the refresh, I am getting error as empty object cannot be converted. So in M code, I want to check for empty check, if not empty then that many rows should get created based on th list count. 

[ykannan · Accepted solution]

I found a simple solution for this . This will save others friend's lot of time. If Microsoft added this step as part of ExpandListColumn, would be great.

Solution:

//Just replace the empty value to empty list as below, that is it.

#"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","",{""},Replacer.ReplaceValue,{"order_id"}),

//Then expand the column and refresh without that error.
#"Expanded" = Table.ExpandListColumn(#"Replaced Value", "order_id")