Forum Discussion
Helper IHow to expand list column which has empty data
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
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")
5 Replies
- Dhacd
Resolver III
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
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")
- ykannan
Can someone please help on this?
