Hi.I have been looking through the Search for a way to accomplish my desired format manipulation but I'm not sure that I'm using the appropriate terminology. Is it possible to perform the following data conversion when data is imported?I would like to take data that is exported in this format;Event IdRes TypeActionResponse Time468745MAOnScene4/1/2020 5:29468745MADeparted4/1/2020 5:31468746TWOnScene4/1/2020 5:58468746TWDeparted4/1/2020 6:53468746PEnRoute4/1/2020 5:34468746POnScene4/1/2020 5:37468746PDeparted4/1/2020 6:53468746FEnRoute4/1/2020 5:34468746FOnScene4/1/2020 5:36468746FDeparted4/1/2020 6:23468746EEnRoute4/1/2020 5:34468746EOnScene4/1/2020 5:37468746EDeparted4/1/2020 6:23468746MANotified4/1/2020 5:34468746MAEnRoute4/1/2020 5:34468746MAOnScene4/1/2020 5:40468746MADeparted4/1/2020 5:41468746MANotified4/1/2020 5:34468746MAEnRoute4/1/2020 5:34468746MAOnScene4/1/2020 5:41468746MADeparted4/1/2020 6:53468746TWOnScene4/1/2020 6:31468746TWDeparted4/1/2020 6:53468747MAOnScene4/1/2020 5:56468747MADeparted4/1/2020 5:56468748MAOnScene4/1/2020 6:00468748MADeparted4/1/2020 6:02 So that it reads in this format;EventIDResp1TypeResp1NotifiedResp1EnRouteResp1OnSceneResp1DepartedResp2TypeResp2NotifiedResp2EnRouteResp2OnSceneResp2DepartedResp3TypeResp3NotifiedResp3EnRouteResp3OnSceneResp3DepartedResp4TypeResp4NotifiedResp4EnRouteResp4OnSceneResp4DepartedResp5TypeResp5NotifiedResp5EnRouteResp5OnSceneResp5DepartedResp6TypeResp6NotifiedResp6EnRouteResp6OnSceneResp6DepartedResp7TypeResp7NotifiedResp7EnRouteResp7OnSceneResp7Departed468745MA 4/1/2020 5:294/1/2020 5:31 468746TW 4/1/2020 5:584/1/2020 6:53P 4/1/2020 5:344/1/2020 5:374/1/2020 6:53F 4/1/2020 5:344/1/2020 5:364/1/2020 6:23E 4/1/2020 5:344/1/2020 5:374/1/2020 6:23MA4/1/2020 5:344/1/2020 5:344/1/2020 5:404/1/2020 5:41MA4/1/2020 5:344/1/2020 5:344/1/2020 5:414/1/2020 6:53TW 4/1/2020 6:314/1/2020 6:53468747MA 4/1/2020 5:564/1/2020 5:56 468748MA 4/1/2020 6:004/1/2020 6:02

Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

Convert Data Entered as Column Entries to Row Entries

Hi.

I have been looking through the Search for a way to accomplish my desired format manipulation but I'm not sure that I'm using the appropriate terminology. Is it possible to perform the following data conversion when data is imported?

I would like to take data that is exported in this format;

Event Id	Res Type	Action	Response Time
468745	MA	OnScene	4/1/2020 5:29
468745	MA	Departed	4/1/2020 5:31
468746	TW	OnScene	4/1/2020 5:58
468746	TW	Departed	4/1/2020 6:53
468746	P	EnRoute	4/1/2020 5:34
468746	P	OnScene	4/1/2020 5:37
468746	P	Departed	4/1/2020 6:53
468746	F	EnRoute	4/1/2020 5:34
468746	F	OnScene	4/1/2020 5:36
468746	F	Departed	4/1/2020 6:23
468746	E	EnRoute	4/1/2020 5:34
468746	E	OnScene	4/1/2020 5:37
468746	E	Departed	4/1/2020 6:23
468746	MA	Notified	4/1/2020 5:34
468746	MA	EnRoute	4/1/2020 5:34
468746	MA	OnScene	4/1/2020 5:40
468746	MA	Departed	4/1/2020 5:41
468746	MA	Notified	4/1/2020 5:34
468746	MA	EnRoute	4/1/2020 5:34
468746	MA	OnScene	4/1/2020 5:41
468746	MA	Departed	4/1/2020 6:53
468746	TW	OnScene	4/1/2020 6:31
468746	TW	Departed	4/1/2020 6:53
468747	MA	OnScene	4/1/2020 5:56
468747	MA	Departed	4/1/2020 5:56
468748	MA	OnScene	4/1/2020 6:00
468748	MA	Departed	4/1/2020 6:02

So that it reads in this format;

EventID

Resp1Type

Resp1Notified

Resp1EnRoute

Resp1OnScene

Resp1Departed

Resp2Type

Resp2Notified

Resp2EnRoute

Resp2OnScene

Resp2Departed

Resp3Type

Resp3Notified

Resp3EnRoute

Resp3OnScene

Resp3Departed

Resp4Type

Resp4Notified

Resp4EnRoute

Resp4OnScene

Resp4Departed

Resp5Type

Resp5Notified

Resp5EnRoute

Resp5OnScene

Resp5Departed

Resp6Type

Resp6Notified

Resp6EnRoute

Resp6OnScene

Resp6Departed

Resp7Type

Resp7Notified

Resp7EnRoute

Resp7OnScene

Resp7Departed

468745

4/1/2020 5:29

4/1/2020 5:31

468746

4/1/2020 5:58

4/1/2020 6:53

4/1/2020 5:34

4/1/2020 5:37

4/1/2020 6:53

4/1/2020 5:34

4/1/2020 5:36

4/1/2020 6:23

4/1/2020 5:34

4/1/2020 5:37

4/1/2020 6:23

4/1/2020 5:34

4/1/2020 5:40

4/1/2020 5:41

4/1/2020 5:34

4/1/2020 5:41

4/1/2020 6:53

4/1/2020 6:31

4/1/2020 6:53

468747

4/1/2020 5:56

468748

4/1/2020 6:00

4/1/2020 6:02

Need Help

camargos88

5 years ago

Anonymous ,

Based on the Excel example, I've removed the Responder Id column.

Try this new m code:

let
    Source = Csv.Document(File.Contents("D:\Downloads\Book2.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Event Id] <> "")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Responder Id"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Event Id", Int64.Type}, {"Responder Type", type text}, {"Name", type text}, {"Action", type text}, {"Response Time", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Event Id", "Action"}, {{"Rows", each Table.AddIndexColumn(_, "Index", 1,1), type table}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Rows"}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows", {"Event Id", "Responder Type", "Name", "Action", "Response Time", "Index", "Res Type"}, {"Event Id",  "Responder Type", "Name", "Action", "Response Time", "Index", "Res Type"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Rows", {"Event Id", "Name", "Action", "Response Time", "Index"}, "Item", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Event Id", "Action", "Response Time", "Index", "Item", "Value"}, "Attribute", "Value.1"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns1", "Index", "Index - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Index", "Index - Copy.1"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column1", {{"Index - Copy", type text}}, "pt-BR"),{"Item", "Index - Copy"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Item"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Index", type text}}, "pt-BR"),{"Action", "Index"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Action.1"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Index - Copy.1", type text}}, "pt-BR"),{"Attribute", "Index - Copy.1"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Name"),
    Union = 
        Table.Combine({
                Table.RenameColumns(Table.SelectColumns(#"Merged Columns2", {"Event Id", "Response Time", "Action.1"}), {{"Response Time", "Value"}, {"Action.1", "Item"}}),
                Table.Distinct(Table.SelectColumns(#"Merged Columns2", {"Event Id", "Item", "Value"}), {"Event Id", "Item"}),
                Table.Distinct(Table.RenameColumns(Table.SelectColumns(#"Merged Columns2", {"Event Id", "Name", "Value.1"}), {{"Name", "Item"}, {"Value.1", "Value"}}), {"Event Id", "Item"})
        
        }),
    #"Pivoted Column" = Table.Pivot(Union, List.Distinct(Union[Item]), "Item", "Value")
in
   #"Pivoted Column"

19 Replies

camargos88
Community Champion
5 years ago
Hi Anonymous ,

Check the attached file:

sltraffic.pbix25 KB
- Anonymous
  Not applicable
  5 years ago
  Thank you for the suggestion, unfortunately, this isn't exactly what I was wanting to do. I want to take the raw data as multiple row entries and convert it to a single row entry.
  
  Any other suggestions?
  - camargos88
    Community Champion
    5 years ago
    Anonymous ,
    
    I am sorry, I didn't get what you need, can you explain it more ? Example with data would be great.

camargos88

Community Champion

5 years ago

Anonymous ,

Based on the Excel example, I've removed the Responder Id column.

Try this new m code:

let
    Source = Csv.Document(File.Contents("D:\Downloads\Book2.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Filtered Rows" = Table.SelectRows(#"Promoted Headers", each ([Event Id] <> "")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Responder Id"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Event Id", Int64.Type}, {"Responder Type", type text}, {"Name", type text}, {"Action", type text}, {"Response Time", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Event Id", "Action"}, {{"Rows", each Table.AddIndexColumn(_, "Index", 1,1), type table}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Rows"}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows", {"Event Id", "Responder Type", "Name", "Action", "Response Time", "Index", "Res Type"}, {"Event Id",  "Responder Type", "Name", "Action", "Response Time", "Index", "Res Type"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Rows", {"Event Id", "Name", "Action", "Response Time", "Index"}, "Item", "Value"),
    #"Unpivoted Columns1" = Table.UnpivotOtherColumns(#"Unpivoted Columns", {"Event Id", "Action", "Response Time", "Index", "Item", "Value"}, "Attribute", "Value.1"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns1", "Index", "Index - Copy"),
    #"Duplicated Column1" = Table.DuplicateColumn(#"Duplicated Column", "Index", "Index - Copy.1"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column1", {{"Index - Copy", type text}}, "pt-BR"),{"Item", "Index - Copy"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Item"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Index", type text}}, "pt-BR"),{"Action", "Index"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Action.1"),
    #"Merged Columns2" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns1", {{"Index - Copy.1", type text}}, "pt-BR"),{"Attribute", "Index - Copy.1"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Name"),
    Union = 
        Table.Combine({
                Table.RenameColumns(Table.SelectColumns(#"Merged Columns2", {"Event Id", "Response Time", "Action.1"}), {{"Response Time", "Value"}, {"Action.1", "Item"}}),
                Table.Distinct(Table.SelectColumns(#"Merged Columns2", {"Event Id", "Item", "Value"}), {"Event Id", "Item"}),
                Table.Distinct(Table.RenameColumns(Table.SelectColumns(#"Merged Columns2", {"Event Id", "Name", "Value.1"}), {{"Name", "Item"}, {"Value.1", "Value"}}), {"Event Id", "Item"})
        
        }),
    #"Pivoted Column" = Table.Pivot(Union, List.Distinct(Union[Item]), "Item", "Value")
in
   #"Pivoted Column"

Anonymous
Not applicable
5 years ago
Wow that was quick. This is awesome! Exactly what I was looking for and works great on other raw files. I can't thank you enough for being patient and taking the time to work with me.

camargos88
Community Champion
5 years ago
Anonymous ,

Which visualization are you trying to achieve ?

I'm asking it because you may have problems with some calculations when you get this model.
- Anonymous
  Not applicable
  5 years ago
  I'm not entirely sure. I was waiting to see if I could format my database in the manner I've mentioned. Bar chart showing the total MA entries to start and then see what other options I can develop.
camargos88
Community Champion
5 years ago
Anonymous ,

Check the attached file.

sltraffic2.pbix240 KB
Anonymous
Not applicable
5 years ago
Would it be possible to save this version down to the 2.78 (Feb 2020) version. I have to go through my Admin to update to the newest version and it's likely to take a while to accomplish?

camargos88

Community Champion

5 years ago

Anonymous ,

I don't have this option. However paste this code on Edit Queries -> New Source -> Blank Query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("xZNPC4IwGIe/iuwsOOc2zVvQhA79wYQO4iFsgYem5Ar69q06lPpqu3WYjO3x97y6d3mOxE0q7SyPyEWpbJ3s3kgznZe6qtV7ralVK52sOktUuDmiPAopM1uruXls1K6U6vkK9XyPYIIdFpMZQC5kc7hoeeyigf9BudnK9mOhLAJIKJTHLOiiWzOESuur7mUGdAiC8iAcgnbuxNadjLr5EITdpOcWtm5h+93C2v0683Wtq1M1OHMKoFZ1jrccxQAJtxz1/1sppLfrpbHLweFr9Ds0nKyUcYCE/+k3Gk2E8hhjgIQrxQQVxQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t, #"(blank).3" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}, {"(blank).3", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Event Id", Int64.Type}, {"Res Type", type text}, {"Action", type text}, {"Response Time", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type1", {"Event Id", "Action"}, {{"Rows", each Table.AddIndexColumn(_, "Index", 1,1), type table}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Rows"}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows", {"Event Id", "Res Type", "Action", "Response Time", "Index"}, {"Event Id", "Res Type", "Action", "Response Time", "Index"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Rows", {"Event Id", "Action", "Response Time", "Index"}, "Attribute", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Index", "Index - Copy"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Index - Copy", type text}}, "pt-BR"),{"Attribute", "Index - Copy"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"RespType"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Index", type text}}, "pt-BR"),{"Action", "Index"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Action.1"),
    Union = 
        Table.Combine({
                Table.RenameColumns(Table.SelectColumns(#"Merged Columns1", {"Event Id", "Response Time", "Action.1"}), {{"Response Time", "Value"}, {"Action.1", "Item"}}),
                Table.Distinct(Table.RenameColumns(Table.SelectColumns(#"Merged Columns1", {"Event Id", "RespType", "Value"}), {{"RespType", "Item"}}), {"Event Id", "Item"})
        
        }),
    #"Pivoted Column" = Table.Pivot(Union, List.Distinct(Union[Item]), "Item", "Value")
in
    #"Pivoted Column"

Anonymous
Not applicable
5 years ago
That's awesome...you are a Super User! That worked exactly how I envisioned.

Only problem, and it's not something that you provided. I want to apply it to my actual raw data file but it contains more columns than I provided. I thought I would be able to expand it but the code is more complicated than I imagined. What would the code be for this format and where in the Steps would I copy/paste it? This file has been reduced to what will end up being a single row with the corresponding entries. Here's a dropbox link to the file.
https://www.dropbox.com/s/3shkz9p481vx57r/responders2.csv?dl=0

If this is asking too much feel free to say so...I would completely understand.

camargos88

Community Champion

5 years ago

Anonymous ,

I'm not sure if it's the result you want:

let
    Source = Csv.Document(File.Contents("D:\Downloads\responders2.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Event Id", Int64.Type}, {"Responder Id", Int64.Type}, {"Responder Type", type text}, {"Name", type text}, {"Action", type text}, {"Response Time", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Event Id", "Action"}, {{"Rows", each Table.AddIndexColumn(_, "Index", 1,1), type table}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"Rows"}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows", {"Event Id", "Responder Id", "Responder Type", "Name", "Action", "Response Time", "Index", "Res Type"}, {"Event Id", "Responder Id", "Responder Type", "Name", "Action", "Response Time", "Index", "Res Type"}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Expanded Rows", {"Event Id", "Responder Id", "Name", "Action", "Response Time", "Index"}, "Item", "Value"),
    #"Duplicated Column" = Table.DuplicateColumn(#"Unpivoted Columns", "Index", "Index - Copy"),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Duplicated Column", {{"Index - Copy", type text}}, "pt-BR"),{"Item", "Index - Copy"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Item"),
    #"Merged Columns1" = Table.CombineColumns(Table.TransformColumnTypes(#"Merged Columns", {{"Index", type text}}, "pt-BR"),{"Action", "Index"},Combiner.CombineTextByDelimiter("_", QuoteStyle.None),"Action.1"),
    Union = 
        Table.Combine({
                Table.RenameColumns(Table.SelectColumns(#"Merged Columns1", {"Event Id", "Responder Id", "Name", "Response Time", "Action.1"}), {{"Response Time", "Value"}, {"Action.1", "Item"}}),
                Table.Distinct(Table.SelectColumns(#"Merged Columns1", {"Event Id", "Responder Id", "Name", "Item", "Value"}), {"Event Id", "Item"})
        
        }),
    #"Pivoted Column" = Table.Pivot(Union, List.Distinct(Union[Item]), "Item", "Value")
in
   #"Pivoted Column"

Just change the path in the beginning:

Source = Csv.Document(File.Contents("D:\Downloads\responders2.csv"),[Delimiter=",", Columns=6, Encoding=1252, QuoteStyle=QuoteStyle.None]),

Anonymous
Not applicable
5 years ago
The previous version was closer. Here is a link to the raw file and the final format desired.
https://www.dropbox.com/s/qp4g4xsim5yfh8r/Example.zip?dl=0

camargos88
Community Champion
5 years ago
Anonymous ,

I got this result, only changing the source:
- Anonymous
  Not applicable
  5 years ago
  I am able to get it to work using the Raw Format file but when I attempt to change the source to the following file, Book2.csv, it doesn't produce the same results. I'm getting multiple row entries for Event Id 468738.
  
  Here are the raw files and resulting formated file.
  https://www.dropbox.com/s/4sgvdica6731m5e/Book2%20Final%20Format.zip?dl=0
camargos88
Community Champion
5 years ago
Anonymous ,

That's because you have different values on Respond Id and Name for the Event Id 468738.
- Anonymous
  Not applicable
  5 years ago
  Yeah I see what you're saying. I was hoping to get those on one row. You've done so much to this point I hate to ask too much more. I really appreciate all your help.
camargos88
Community Champion
5 years ago
Anonymous ,

I'm happy it worked!!!

Forum Discussion

Convert Data Entered as Column Entries to Row Entries

19 Replies

Related content

Converting to Percentage

Calculation of Total Amount With Sales Entry

Convert String from Database Entries into Matrix Visual

Automate data entry in excel

Unique Count in Row convert