Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have another challange for which I need your assistance.
I have the following table
Trip Number | Full Itinerary |
3564 | (01)GENEVA,GE,SWITZERLAND to SKOPJE,FYRO MACEDONIA > , (02)SKOPJE,FYRO MACEDONIA to GENEVA,GE,SWITZERLAND > |
529 | (01)PANAMA CITY,PANAMA to AMSTERDAM,NETHERLANDS > , (02)AMSTERDAM,NETHERLANDS to PARIS,75,FRANCE > , (03)PARIS,75,FRANCE to AMSTERDAM,NETHERLANDS > , (04)AMSTERDAM,NETHERLANDS to PANAMA CITY,PANAMA > |
I want to create a new table in power BI with this result
Trip number | Origin | Destination | Leg |
3564 | GENEVA,GE,SWITZERLAND | SKOPJE,FYRO MACEDONIA | 1 |
3564 | SKOPJE,FYRO MACEDONIA | GENEVA,GE,SWITZERLAND | 2 |
529 | PANAMA CITY,PANAMA | AMSTERDAM,NETHERLANDS | 1 |
529 | AMSTERDAM,NETHERLANDS | PARIS,75,FRANCE | 2 |
529 | PARIS,75,FRANCE | AMSTERDAM,NETHERLANDS | 3 |
529 | AMSTERDAM,NETHERLANDS | PANAMA CITY,PANAMA | 4 |
I do not know how many items the full itinerary has but the constant delimiter is the ">" and the "to" for origin and destination. If I split this in edit query I get of course many empty columns this is why I want to work by rows.
Does anyone have any ideas?
Thanks,
Mike
Solved! Go to Solution.
HI @Mike22,
I don't think they can achieve through dax formula, you can take a look at following query query formula about transform format:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY/LCsIwEEV/ZciqhVnEPhSXQzqtUZOWJFRq7dK1G/1/CxbxUcTdwNxzH30v0nyZCRSRXMQVW24JK0Z/0OHIbk+2gOsF/K5utoxl52owpLiorSY43aRMz4AQySSel4zsvOnEigF7kSfrqUBDlgyB0qHD6R4dyPjAriCDlsPmYeDf0+clI9uQ0x5XOZaOrOJXKo0/n/9kZb+yvto/Zw53", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Trip Number" = _t, #"Full Itinerary" = _t]), #"Added Custom" = Table.AddColumn(Source, "Itinerary", each List.RemoveLastN(List.Transform(Text.Split([Full Itinerary],">"),each Text.Trim(Text.End(_,Text.Length(_)-Text.PositionOf(_,")")-1)," ")),1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Full Itinerary"}), #"Expanded Itinerary" = Table.ExpandListColumn(#"Removed Columns", "Itinerary"), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Itinerary", "Itinerary", Splitter.SplitTextByDelimiter("to", QuoteStyle.Csv), {"Origin", "Destination"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Trip Number", Int64.Type}, {"Origin", type text}, {"Destination", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Trip Number"}, {{"Contents", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}), #"Expanded Contents" = Table.ExpandTableColumn(#"Grouped Rows", "Contents", {"Origin", "Destination", "Index"}, {"Origin", "Destination", "Index"}) in #"Expanded Contents"
Regards,
Xiaoxin Sheng
Hey,
I am trying to split 3 columns by delimiter. How can I create 2 rows with the new info after the & delimiter without duplicating everything 6 times?
My data looks like this
Installed model Capacity Category
Unit A & Unit B 5.00 & 2.50 2 & 1
Should look like
Unit A 5.00 2
Unit B 2.5 1
HI @Mike22,
I don't think they can achieve through dax formula, you can take a look at following query query formula about transform format:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jY/LCsIwEEV/ZciqhVnEPhSXQzqtUZOWJFRq7dK1G/1/CxbxUcTdwNxzH30v0nyZCRSRXMQVW24JK0Z/0OHIbk+2gOsF/K5utoxl52owpLiorSY43aRMz4AQySSel4zsvOnEigF7kSfrqUBDlgyB0qHD6R4dyPjAriCDlsPmYeDf0+clI9uQ0x5XOZaOrOJXKo0/n/9kZb+yvto/Zw53", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Trip Number" = _t, #"Full Itinerary" = _t]), #"Added Custom" = Table.AddColumn(Source, "Itinerary", each List.RemoveLastN(List.Transform(Text.Split([Full Itinerary],">"),each Text.Trim(Text.End(_,Text.Length(_)-Text.PositionOf(_,")")-1)," ")),1)), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Full Itinerary"}), #"Expanded Itinerary" = Table.ExpandListColumn(#"Removed Columns", "Itinerary"), #"Split Column by Delimiter" = Table.SplitColumn(#"Expanded Itinerary", "Itinerary", Splitter.SplitTextByDelimiter("to", QuoteStyle.Csv), {"Origin", "Destination"}), #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Trip Number", Int64.Type}, {"Origin", type text}, {"Destination", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Trip Number"}, {{"Contents", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}), #"Expanded Contents" = Table.ExpandTableColumn(#"Grouped Rows", "Contents", {"Origin", "Destination", "Index"}, {"Origin", "Destination", "Index"}) in #"Expanded Contents"
Regards,
Xiaoxin Sheng
Thanks @v-shex-msft
for your usual very detailed reply.
Unfortunately I am getting the error below
Expression.Error: We cannot convert the value null to type Text. Details: Value= Type=Type
When on the step
= Table.Group(#"Changed Type", {"Trip Number"}, {{"Contents", each Table.AddIndexColumn(_, "Index", 1, 1), type table}}),
I checked and there are no null values in the trip number do you have any idea of what it could be?
Thanks a lot.
Mike
HI @Mike22,
Can you please share your pbix file with sample source data to test?
Regards,
Xiaoxin Sheng
I was about to write to you, I just found the issue. The excel source file had 2 completely blank rows that could not be seen as null when filtering on the row but were impacting the calculation.
Thanks a lot for another great solution.
Mike
Hello @v-shex-msft
I am sorry to go back to this topic which was resolved. I have a quick question as I continue my work on this dataset. I would like to now represent this data on a map showing the various flight paths. Ideally, I want also to show the bubble on each city to count the number of flights to the specific city. I do not want to count the last leg back to the origin, but I want to still display it on the map.
I have seen some custom maps that show flight paths but I am not sure how to adapt my data and also which one is the best to show what I am aiming for.
Thanks a lot in advance for any help,
Mike
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |